Mysql Left Join Not Producing Expected Results

I know there is a lot of good questions answered on Left Joins, but I am having trouble finding something pertaining to my particular problem. I apologize for a possible repeated t

Solution 1:

Simply move the B.business_date = '2011-10-06' condition from the WHERE to the ON clause. When you have a LEFT join, a WHERE condition about the second table's columns (except IS (NOT) NULL ones) actually cancels the LEFT JOIN, it then acts as an INNER JOIN.

SELECT A.id AS ID
     , A.gift_cards_sold AS A_SOLD
     , B.gift_cards_sold AS B_SOLD
FROM A
  LEFT JOIN B
    ON  B.id = A.id
    AND B.business_date = '2011-10-06'WHERE A.am_pm = 'PM'AND A.business_date = '2011-10-06'ORDERBY A.id

Solution 2:

You should use something like this:

SELECT A.id AS ID, A.gift_cards_sold AS A_SOLD, B.gift_cards_sold AS B_SOLD
FROM A
LEFT JOIN B
USING (id)
WHERE A.am_pm = 'PM'AND A.business_date = '2011-10-06'AND (B.business_date = '2011-10-06' or B.business_date is null)GROUPBY A.id
ORDERBY A.id ASC

Solution 3:

I'm not sure why you were using a "group by" clause - it's not needed here, and in most other databases, would be a syntax error.

I'm assuming that you only need to match the rows on id.

This should work:

select A.id AS ID, A.gift_cards_sold AS A_SOLD, B.gift_cards_sold AS B_SOLD
 from A left join b on b.id = a.id
where A.business_date = '2011-10-06'  and A.am_pm = 'PM'orderby a.id

Solution 4:

Problem is with the where condition for B.business_date, it should also allow null

SELECT A.id AS ID, A.gift_cards_sold AS A_SOLD, B.gift_cards_sold AS B_SOLD
FROM A LEFTJOIN B ON A.id = B.id
WHERE A.am_pm ='PM'AND A.business_date ='2011-10-06'AND (B.business_date ='2011-10-06'OR B.business_date isnull )
ORDERBY A.id ASC