MySql full join (join) and sorting by multiple date columns

Question

A rather complicated sql query, which I can do is much more complicated than it should be: I have two tables:

News: newsid, datetime, newstext

Photo: pictureid, datetime, imgPath

Both are not related to each other, I join only the date the news / image was created on

SQL:

SELECT * FROM news as n LEFT OUTER JOIN (SELECT count(pictureid), datetime FROM picture GROUP BY DATE(datetime)) as p ON DATE(n.datetime) = DATE(p.datetime) UNION SELECT * FROM news as n RIGHT OUTER JOIN (SELECT count(pictureid), datetime FROM picture GROUP BY DATE(datetime)) as p ON DATE(n.datetime) = DATE(p.datetime) 

I need to use union to simulate a full outer join in MySQL. Results:

 newsid text datetime count() datetime 1 sometext 2011-01-16 1 2011-01-16 2 moo2 2011-01-19 NULL NULL 3 mooo3 2011-01-19 NULL NULL NULL NULL NULL 4 2011-01-14 

The problem is that I obviously get two date columns - one of the news and one of the images, which means that I cannot sort by date and be in the correct order! Any ideas? Even if it means restructuring the database! I need a date to be in one column.

Reply from SeRPRo Completed working code:

 SELECT `newsid`, `text`, CASE WHEN `datetime` IS NULL THEN `pdate` ELSE `datetime` END as `datetime`, `pcount` FROM ( (SELECT * FROM news as n LEFT OUTER JOIN (SELECT count(pictureid) as pcount, datetime as pdate FROM picture GROUP BY DATE(datetime)) as p ON DATE(n.datetime) = DATE(p.pdate) ORDER BY datetime ) UNION (SELECT * FROM news as n RIGHT OUTER JOIN (SELECT count(pictureid) as pcount, datetime as pdate FROM picture GROUP BY DATE(datetime)) as p ON DATE(n.datetime) = DATE(p.pdate) ORDER BY datetime ) ) as x ORDER BY datetime 

Answer 1

just using the database structure and your query, and since the FULL OUTER JOIN is not available in MySQL, I think the solution could be as follows:

 SELECT `newsid`, `text`, CASE WHEN `datetime` IS NULL THEN `pdate` ELSE `datetime` END as `datetime, `pcount` ( SELECT * FROM `news` as `n` LEFT OUTER JOIN ( SELECT count(pictureid) as `pcount`, datetime as `pdate` FROM picture GROUP BY DATE(datetime) ) as p ON DATE(n.datetime) = DATE(p.datetime) UNION SELECT * FROM `news` as `n` RIGHT OUTER JOIN ( SELECT count(pictureid) as `pcount`, datetime as `pdate` FROM picture GROUP BY DATE(datetime) ) as p ON DATE(n.datetime) = DATE(p.datetime) ) 

Answer 2

I think that your code would solve this definitively, and you could explain the datetime sorting part in more detail

 SELECT * FROM News FULL OUTER JOIN Picture ON News.Datetime = Picture.Datetime ORDER BY DateTime Asc 

Answer 3

You can always wrap a request with another:

 SELECT ..., IF(datetime1 IS NULL, datetime2, datetime1) as datetime, ... FROM ( ... your query ... ) ORDER BY datetime