How to use mySQL union (full join) and order by DATE of just news?

[Charli Wilson]

Hello! A rather complicated sql query that I might be makign much more difficult that it should be:
I have two tables:

News:
newsid, datetime, newstext

Picture:
pictureid, datetime, imgPath

Ideally I want a query to return the following kind of table:

newsid, newstext, numImages, date (date only- no time)
1       someText   4          2010-01-01
null      null     5          2010-02-01
 

in the results i want a row for every news post with corresponding pictures of they exist- but also a row for the number of images if there was no news.

SQL so far:

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 have to use union to simulate a full outer join- I want to know when there were pictures even if there was not a post that day.
The 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 being that I obviously end up with two date columns- one from news and one from pictures, this means i cannot order by date and have it be in the correct order! Any ideas? Even if it means restructuring the database!

[mwasif]

Does a picture always belong to a news or not?

[Charli Wilson]

No it doesn't- the only way pictures are joined is by the date they're added on. They don't belong to any particular news.