CODEWITHSUNDEEP
mysqlsql
itsreeyan
itsreeyan

Reputation: 174

Issue with left join

I'm sure this is an easy one, but I've been looking at it for an hour and just can't see it.

I have two tables on a sales/payment system, one that contains the different payment methods and associated details, and another that contains the sales including ID's, totals and payment method.

At the end of the period, I want to bring up the total for all the different methods, or NULL or 0 if there was none.

This is the query I have:

SELECT m.method_id, m.description, SUM(s.total) as total 
FROM payment_method m 
         LEFT JOIN sale s ON s.payment_method = m.method_id 
 WHERE m.for_stock_keeping = 0 AND 
 (s.shift_id = ? OR s.shift_id IS NULL) 
 GROUP BY m.method_id;

It works fine until we start another shift with a new ID, then any payment methods that have been used in a previous shift no longer show up if no sales occur for that method. i.e.: If I make 10 cash sales this shift, and then start a new shift Cash will no longer show up with a NULL value, but will only appear after a Cash sale has occurred and there is a value for it. This is not the behaviour I want.

Any help would be really appreciated!

Thanks, Ryan.

Upvotes: 0

Views: 62

Answers (3)

TetonSig
TetonSig

Reputation: 2227

Any criteria in your WHERE clause that relates to the table on the right side of a LEFT JOIN is going to mean the query returns 0 rows when nothing matches the JOIN, (because those fields will not exist)

so when you say

WHERE 

(s.shift_id = ? OR s.shift_id IS NULL)

there is no row from s to check in a new shift with no payments, so this criteria will always fail.

So, just move the check up to the JOIN and you'll be fine.

SELECT m.method_id, m.description, SUM(s.total) as total 
FROM payment_method m 
         LEFT JOIN sale s ON s.payment_method = m.method_id AND (s.shift_id = ? OR s.shift_id IS NULL)
 WHERE m.for_stock_keeping = 0  
 GROUP BY m.method_id;

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

The condition on s.shift_id in the WHERE clause is applied after the outer join; you need it applied before.

You have two options (at least):

SELECT m.method_id, m.description, SUM(s.total) as total 
  FROM payment_method m 
  LEFT JOIN sale s ON s.payment_method = m.method_id AND 
                       (s.shift_id = ? OR s.shift_id IS NULL)
 WHERE m.for_stock_keeping = 0 
 GROUP BY m.method_id, m.description;

and:

SELECT m.method_id, m.description, SUM(s.total) as total 
  FROM payment_method m 
  LEFT JOIN (SELECT * FROM Sale AS a WHERE a.shift_id = ? OR a.shift_id IS NULL) AS s
    ON s.payment_method = m.method_id 
 WHERE m.for_stock_keeping = 0 
 GROUP BY m.method_id, m.description;

Upvotes: 1

gnassas
gnassas

Reputation: 1

The query looks OK but perhaps your DBMS is fussy about m.method_id being neither an aggregate function nor in the group by clause. Try this:

SELECT m.method_id, m.description, SUM(s.total) as total
FROM payment_method m LEFT JOIN sale s ON s.payment_method = m.method_id
WHERE m.for_stock_keeping = 0 AND (s.shift_id = ? OR s.shift_id IS NULL)
GROUP BY m.method_id, m.description;

Upvotes: 0

Related Questions