mysql left join not producing null

secretlydead · 04-24-2012, 10:29 AM

i've got this query:

Code:

SELECT DISTINCT seo.keyword, prev.google AS googlePrev, prev.yahoo AS yahooPrev, prev.bing AS bingPrev, seo.google AS googleCur, seo.yahoo AS yahooCur, seo.bing AS bingCur
 FROM seo_rankingreports AS seo
 LEFT JOIN seo_rankingreports AS prev
 ON seo.keyword = prev.keyword AND prev.client_id = seo.client_id AND prev.campaign = seo.campaign
 WHERE seo.client_id = '284' AND seo.campaign = 'Focus' AND seo.domain = 'www.domain.com' AND prev.report_date = '2012-02-01' AND seo.report_date = '2012-03-01'

You can find a highlighted version of this at:
http://pastebin.com/y4Q3juYh

It works when there is data for the previous month but not when there is no data.

when there is no data for the previous month, this is what I would like returned:

Code:

+-------------------------------+------------+-----------+----------+-----------+----------+---------+
| keyword                       | googlePrev | yahooPrev | bingPrev | googleCur | yahooCur | bingCur |
+-------------------------------+------------+-----------+----------+-----------+----------+---------+
| astm structural steel         |       NULL |      NULL |     NULL |        15 |        0 |      45 | 
| iso structural steel          |       NULL |      NULL |     NULL |         2 |        6 |       5 | 
| asme structural steel         |       NULL |      NULL |     NULL |         5 |       17 |      13 | 
| api line pipe                 |       NULL |      NULL |     NULL |         6 |       17 |      16 | 
| steel tubing supplier         |       NULL |      NULL |     NULL |         0 |       78 |      87 | 
| api 5l line pipe              |       NULL |      NULL |     NULL |        35 |       13 |      12 | 
| octg casing                   |       NULL |      NULL |     NULL |         0 |        0 |      93 | 
| octg tubing                   |       NULL |      NULL |     NULL |        20 |       36 |      35 | 
| line pipe manufacturer        |       NULL |      NULL |     NULL |         9 |        0 |       0 | 
| octg line pipe                |       NULL |      NULL |     NULL |        19 |       17 |      16 | 
| octg steel                    |       NULL |      NULL |     NULL |        14 |        5 |       5 | 
| iso standard pipe             |       NULL |      NULL |     NULL |        13 |        0 |      88 | 
| iso tubing                    |       NULL |      NULL |     NULL |        13 |        7 |      24 | 
| steel tubing distributor      |       NULL |      NULL |     NULL |        80 |       30 |      32 | 
| drill pipe manufacturers      |       NULL |      NULL |     NULL |         0 |        0 |       0 | 
| oilfield drill pipe           |       NULL |      NULL |     NULL |         0 |        0 |       0 | 
| drill pipe suppliers          |       NULL |      NULL |     NULL |         0 |        0 |       0 | 
| heavy weight drill pipe       |       NULL |      NULL |     NULL |         0 |        0 |       0 | 
| offshore pipeline supplier    |       NULL |      NULL |     NULL |        27 |        0 |       0 | 
| offshore pipeline             |       NULL |      NULL |     NULL |         0 |        0 |       0 | 
| exploration pipeline          |       NULL |      NULL |     NULL |         3 |        0 |       0 | 
| refinery pipeline             |       NULL |      NULL |     NULL |         0 |        0 |       0 | 
| exploration pipeline supplier |       NULL |      NULL |     NULL |        18 |        0 |       0 | 
| refinery pipeline supplier    |       NULL |      NULL |     NULL |         8 |        0 |       0 | 
+-------------------------------+------------+-----------+----------+-----------+----------+---------+

secretlydead · 04-25-2012, 01:18 AM

If you go here, you can see some test data:

mysql -u test2 -p -h 199.101.103.61
p****ord: whatdoievenneedapasswordfor

Here's a pastebin to help with the formatting:
http://pastebin.com/HLeHQin9

I would expect this:

Code:

SELECT DISTINCT seo.keyword, prev.google AS googlePrev, prev.yahoo AS yahooPrev, prev.bing AS bingPrev, seo.google AS googleCur, seo.yahoo AS yahooCur, seo.bing AS bingCur
 FROM seo_rankingreports AS prev
 left JOIN seo_rankingreports AS seo
 ON seo.keyword = prev.keyword AND prev.client_id = seo.client_id AND prev.campaign = seo.campaign
 WHERE seo.client_id = '284' AND seo.campaign = 'Focus' AND seo.domain = 'www.domain1.com' AND prev.report_date = '2012-02-01' AND seo.report_date = '2012-03-01';

to give the same result as this:

Code:

SELECT DISTINCT seo.keyword, prev.google AS googlePrev, prev.yahoo AS yahooPrev, prev.bing AS bingPrev, cur.google AS googleCur, cur.yahoo AS yahooCur, cur.bing AS bingCur
 FROM seo_rankingreports AS seo
 left JOIN
 (SELECT keyword,google,yahoo,bing FROM seo_rankingreports WHERE client_id = '284' AND campaign = 'Focus' AND domain = 'www.domain1.com' AND report_date = '2012-02-01') AS prev
 ON seo.keyword = prev.keyword
 inner JOIN
 (SELECT keyword,google,yahoo,bing FROM seo_rankingreports WHERE client_id = '284' AND campaign = 'Focus' AND domain = 'www.domain1.com' AND report_date = '2012-03-01') AS cur
 ON seo.keyword = cur.keyword;

Here's a good example of why the query doesn't always work.

Code:

SELECT DISTINCT seo.keyword, prev.google AS googlePrev, prev.yahoo AS yahooPrev, prev.bing AS bingPrev, cur.google AS googleCur, cur.yahoo AS yahooCur, cur.bing AS bingCur
 FROM seo_rankingreports AS seo
 left JOIN (SELECT keyword,google,yahoo,bing FROM seo_rankingreports WHERE client_id = '179' AND campaign = 'All Keywords' AND domain = 'www.domain2.com' AND report_date = '2011-01-01') AS prev
 ON seo.keyword = prev.keyword inner JOIN (SELECT keyword,google,yahoo,bing FROM seo_rankingreports
 WHERE client_id = '179' AND campaign = 'All Keywords' AND domain = 'www.domain2.com' AND report_date = '2011-02-01') AS cur
 ON seo.keyword = cur.keyword;

gives this:

Code:

+-------------------------------------------------+------------+-----------+----------+-----------+----------+---------+
| keyword                                         | googlePrev | yahooPrev | bingPrev | googleCur | yahooCur | bingCur |
+-------------------------------------------------+------------+-----------+----------+-----------+----------+---------+
| fort lauderdale blepharoplasty                  |       NULL |      NULL |     NULL |        26 |        0 |       0 |
| fort lauderdale board certified plastic surgeon |       NULL |      NULL |     NULL |         6 |       35 |      35 |
| fort lauderdale body lift surgery               |       NULL |      NULL |     NULL |         0 |        0 |       0 |
| fort lauderdale boob job                        |       NULL |      NULL |     NULL |         0 |        0 |       0 |
| fort lauderdale boob lift                       |       NULL |      NULL |     NULL |        26 |        0 |       0 |
| fort lauderdale botox                           |       NULL |      NULL |     NULL |        77 |        0 |       0 |
| fort lauderdale botox cosmetic                  |       NULL |      NULL |     NULL |        37 |       81 |      80 |
...
76 rows in set (0.09 sec)

This is a similar query with similar dates, where the previous month should be NULL.
This gives an empty set:

Code:

SELECT DISTINCT seo.keyword, prev.google AS googlePrev, prev.yahoo AS yahooPrev, prev.bing AS bingPrev, seo.google AS googleCur, seo.yahoo AS yahooCur, seo.bing AS bingCur
 FROM seo_rankingreports AS prev
 left JOIN seo_rankingreports
 AS seo
 ON seo.keyword = prev.keyword AND prev.client_id = seo.client_id AND prev.campaign = seo.campaign
 WHERE seo.client_id = '179' AND seo.campaign = 'All Keywords' AND seo.domain = 'www.domain2.com'
 AND prev.report_date = '2011-01-01' AND seo.report_date = '2011-02-01';

This, however, when there IS a previous month of data to call:

Code:

SELECT DISTINCT seo.keyword, prev.google AS googlePrev, prev.yahoo AS yahooPrev, prev.bing AS bingPrev, seo.google AS googleCur, seo.yahoo AS yahooCur, seo.bing AS bingCur
 FROM seo_rankingreports
 AS prev
 left JOIN seo_rankingreports
 AS seo
 ON seo.keyword = prev.keyword AND prev.client_id = seo.client_id AND prev.campaign = seo.campaign
 WHERE seo.client_id = '179' AND seo.campaign = 'All Keywords' AND seo.domain = 'www.domain2.com'
 AND prev.report_date = '2011-02-01'
 AND seo.report_date = '2011-03-01';

returns everything as expected:

Code:

+-------------------------------------------------+------------+-----------+----------+-----------+----------+---------+
| keyword                                         | googlePrev | yahooPrev | bingPrev | googleCur | yahooCur | bingCur |
+-------------------------------------------------+------------+-----------+----------+-----------+----------+---------+
| fort lauderdale blepharoplasty                  |         26 |         0 |        0 |        18 |        0 |       0 |
| fort lauderdale board certified plastic surgeon |          6 |        35 |       35 |         6 |       26 |      26 |
| fort lauderdale body lift surgery               |          0 |         0 |        0 |        84 |       82 |      80 |
| fort lauderdale boob job                        |          0 |         0 |        0 |         0 |        0 |       0 |
| fort lauderdale boob lift                       |         26 |         0 |        0 |        25 |        0 |       0 |
| fort lauderdale botox                           |         77 |         0 |        0 |        37 |        0 |       0 |
| fort lauderdale botox cosmetic                  |         37 |        81 |       80 |        17 |       75 |      82 |

It is when there is no previous month data that the query fails. I would have expected left join to work for this, but it doesn't seem to. How can I fix this?

I'm ready to break down and use PHP conditional statements to do this, but that leads to other problems, and I'd like to do this right and only have one query.