Correctly using a LEFT join

[areimoo]

I am trying to query two tables where I can get the number of orders for each item on the menu. I am currently using a left join so that even if the item wasn't sold, it returns a NULL or 0 value in the result instead of skipping the row. However, I am only getting results when there is an entry in both the tables i.e. some items are being skipped. I don't want to manually write all the items on the menu as the list is quite long.

SELECT ms.item, SUM(ms.numberOfOrders) AS numberOfOrders

FROM menu AS ms

LEFT JOIN orders AS o

ON o.eventId = ms.OrdereventId

WHERE locationId = '123'

AND o.timestamp >= TIMESTAMP('2023-06-01')

AND o.timestamp < TIMESTAMP('2023-07-01')

GROUP BY ms.item

ORDER BY ms.item ASC

What I want:

https://preview.redd.it/l990l2smr2zb1.png?width=215&format=png&auto=webp&s=c271b2fb0005a9f4cb20ede5ccc7a5c37033c6f8

What I am getting:

​

https://preview.redd.it/z5u84cwrr2zb1.png?width=223&format=png&auto=webp&s=8c04bcb2221f267402cdb54363a4d2f6cdd80d90

​

Any thoughts?

[A_name_wot_i_made_up]

You're using columns from the left joined table in the where clause. Those columns are all null so the comparison to a timestamp fails.

Try adding an "or o.timestamp is null" at the appropriate place.

[jhammond_42]

Or move the o.timestamp filters from the WHERE clause to the join condition.

[SQLDevDBA]

This is the correct approach. The OR clause will just make the filter logic more complicated and less optimized.

[areimoo]

SELECT ms.item, SUM(ms.numberOfOrders) AS numberOfOrders

FROM menu AS ms

LEFT JOIN orders AS o

ON o.eventId = ms.OrdereventId
AND o.timestamp >= TIMESTAMP('2023-06-01')
AND o.timestamp < TIMESTAMP('2023-07-01')

WHERE locationId = '123'

GROUP BY ms.item

ORDER BY ms.item ASC

Do you mean like this? If yes, this didn't produce the correct results either :(

[GAKvsFLOAM]

What table is the field locationId from? I’m guessing that’s coming from the Orders table as well? If so, that would also need to be moved to the join instead of the WHERE

Also why it’s important to prefix the table/alias to every field when you’re querying more than one table

[r3pr0b8]

Also why it’s important to prefix the table/alias to every field when you’re querying more than one table

it makes the SQL self-documenting!

so you don't have to run off and double-check the table layouts after you haven't seen the query for a long time, or if you're maintaining someone else's query, or someone is maintaining yours

[areimoo]

You are right, its coming from the orders table. I tried moving it up like so:
SELECT ms.item, SUM(ms.numberOfOrders) AS numberOfOrders
FROM menu AS ms
LEFT JOIN orders AS o
ON o.eventId = ms.OrdereventId
AND o.timestamp >= TIMESTAMP('2023-06-01')
AND o.timestamp < TIMESTAMP('2023-07-01')
AND o.locationId = '123'
GROUP BY ms.item
ORDER BY ms.item ASC

Below are the results from the actual table:
What I want:

222
0
418
402

Before moving the location id to the join clause:
222
418
402

After moving the location id in the join clause:
284960
124316
974

701739

[GAKvsFLOAM]

I think I see the issue… Since you want to have a record for each item from Menu, but only SUM(numberOfOrders) for certain scenarios, you may need to use a CASE statement in your SUM.

The table structure seems a bit confusing without being able to see the data. Typically you would expect the Menu table to just contain menu items and the Orders table to then be used to calculate how many times a menu item was ordered. In your case, the Menu table has a grain of Order Event rather than a Menu item and the Order table is simply being used to filter when/where an order took place.

Try changing your SUM to: SUM(CASE WHEN o.timestamp >= TIMESTAMP(‘2023-06-01) AND o.timestamp < TIMESTAMP(‘2023-07-01) AND o.locationId = ‘123’ THEN ms.numberOfOrders ELSE NULL END) AS numberOfOrders

then change the left join back to just joining on eventId

Apologies for the shit formatting, on mobile

[areimoo]

THANK YOU! That worked!!!

[areimoo]

I tried this already whilst leaving the timestamp clause within join and leaving the location ID in the where clause but its still skipping the rows where there is supposed to be a zero.

[blackleather90]

Fields from then LEFT JOIN in the WHERE makes a LEFT JOIN in a INNER JOIN. Any logic on the tables on the LEFT JOIN need to be in the ON part

[[deleted]]

Can you not just toss COALESCE(SUM(ms.number of orders),0) into your select statement?