6

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

News: newsid, datetime, newstext

Picture: pictureid, datetime, imgPath

The two are not related, I am only joining by the date that the news/picture was created on

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 in MySQL. 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! I need date to be in a single column.

The answer came from SeRPRo The completed working code is:

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
Charli
  • 63
  • 1
  • 4

3 Answers3

3

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

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) 

)
SERPRO
  • 10,015
  • 8
  • 46
  • 63
0

i think your code would resolved to this finally and could you explain the datetime sort part a bit more in detail

SELECT *  
FROM   News FULL OUTER JOIN Picture 
ON News.Datetime = Picture.Datetime ORDER BY DateTime Asc
Deeptechtons
  • 10,945
  • 27
  • 96
  • 178
  • Yeah that is what is resolves to! Just MySQL doesn't seem to be able to do FULL OUTER JOINs, so the UNION and replication is a work around. I need the dates to be all in one column to enable me to order them properly- with two columns (one each from picture and news) and the gaps being nulls the query ends up listing all news, then all pictures. Is that any clearer? – Charli Jan 25 '11 at 11:06
  • What does the datetime in News represent and datetime in Picture represent. Upload dates? If they are different then you might have to concatenate ex:
    select columnA||columnB from TABLEA; (Concats column A and B)
     – Deeptechtons Jan 25 '11 at 11:21
  • Yes, they're just upload dates – Charli Jan 25 '11 at 11:34
0

You can always wrap the query with another:

SELECT ..., IF(datetime1 IS NULL, datetime2, datetime1) as datetime, ... FROM
(
   ... your query ...
)
ORDER BY datetime
Clement P
  • 3,243
  • 1
  • 19
  • 20