CODEWITHSUNDEEP
mysqlleft-join
Mattamatic
Mattamatic

Reputation: 71

MySQL left join returning wrong number of results

hoping someone can help me with this issue. I have a table with the following data:

site_id  type                           key               data
2        organic-keywords-last-month    (not provided)    9064
2        organic-keywords-last-month    bmi               345
2        organic-keywords-last-month    bmi kalkulator    445
2        organic-keywords-last-month    grove pannekaker  678
2        organic-keywords-last-month    grove vafler      976
2        organic-keywords-last-month    lapper            475
2        organic-keywords-last-month    melk.no           624
2        organic-keywords-last-month    ostesuffle        361
2        organic-keywords-last-month    scones            697
2        organic-keywords-last-month    sunne pannekaker  658
2        organic-keywords-last-month    sunne vafler      484
2        organic-keywords-this-month    (not provided)    10034
2        organic-keywords-this-month    bmi kalkulator    659
2        organic-keywords-this-month    grove pannekaker  721
2        organic-keywords-this-month    grove vafler      857
2        organic-keywords-this-month    lapper            515
2        organic-keywords-this-month    melk.no           587
2        organic-keywords-this-month    ostesuffle        433
2        organic-keywords-this-month    scones            626
2        organic-keywords-this-month    smultringer       401
2        organic-keywords-this-month    sunne pannekaker  566
2        organic-keywords-this-month    sunne vafler      407

What I want to do is query for all the organic-keywords-this-month, and left join them to the organic-keywords-last-month, so that I get a list of the 11 keywords from this month, their performance, and their performance last month.

I am using the following query:

SELECT 
    ktm.key AS keyword, 
    ktm.data AS `this month`, 
    klm.data AS `last month` 
FROM
    data AS ktm 
    LEFT JOIN data AS klm ON 
        (ktm.key = klm.key AND ktm.type = 'organic-keywords-this-month' 
        AND klm.type = 'organic-keywords-last-month') 
WHERE 
    ktm.site_id = 2 
    AND klm.site_id = 2 
ORDER BY 
    `this month` ASC

Which I think should return 11 rows, as there are 11 rows of type 'organic-keywords-this-month', yet when I run it it only return 10 rows, and looks more like it is inner joining the tables rather than left join.

Anyone have any ideas?

Upvotes: 2

Views: 399

Answers (4)

DRapp
DRapp

Reputation: 48139

I think the query is very close... but put your join condition on the types being the same too, and only put the QUALIFIER of the type in the WHERE clause associated with THIS MONTHs data

SELECT 
      ktm.key AS keyword, 
      ktm.data AS this_month, 
      klm.data AS last_month 
   FROM 
      data AS ktm 
         LEFT JOIN data AS klm 
             ON klm.site_id = ktm.site_id
            AND klm.key = ktm.key 
            AND klm.type = 'organic-keywords-last-month'
   WHERE 
          ktm.site_id = 2 
      AND ktm.type = 'organic-keywords-this-month' 
   ORDER BY 
     this_month ASC

By applying the "AND ktm.type = 'organic...', that will properly qualify WHAT YOU WANT NOW.

However, the LEFT JOIN will also explicitly match based on "AND ktm.type = klm.type" to the clause. Notice I adjusted to similarly apply your "same site ID" as last month too.

I would ensure the data table has an index on (site_id, type, key)

Upvotes: 2

JM Hicks
JM Hicks

Reputation: 1292

Like Paul Gregory says. I think that should be accepted as an answer. I would just add that the site values should be compared inside the join 'on' clause if and only if you want to restrict comparisons of the key values to those from the same site, which it seems you want. In either case, if you want to restrict results to ktm.site = 2 it's ok to have that in either the join on clause, along with a ktm.site = klm.site, or the where clause, just don't put klm.site in the where clause.

Upvotes: 1

Stephen S
Stephen S

Reputation: 176

If you seperate your table into two tables, one for this-month, one for last-month, you'll notice a slight discrepancy in the key column. The last-month group has a key called 'bmi' which is missing from this-month, and this-month has a key called 'smultringer', which is missing from last-month. Because of the lack of a match, which you specified in klm.key = ktm.key, those rows will be left out of the join, giving you only 10 rows instead of 11.

Edit: I'm not saying to actually seperate the table, I meant to do that mentally to compare the groups.

Upvotes: 0

Paul Gregory
Paul Gregory

Reputation: 1753

Your WHERE requires a particular value in klm, therefore the query is not returning your 11th line that doesn't even have any klm values.

Take the klm half of the WHERE out and put it in the LEFT JOIN.

Upvotes: 1

Related Questions