1

I have three tables item, store and stock_movement. The stock movement has the fields id, item_id, store_id and quantity. I would like to create a view showing the sum of the quantities of each item in each store. If there is no stock_movement entry for an item in a store, the quantity should be zero.

I came up with the following query:

SELECT item.*, store.id, SUM(s.quantity) AS quantity
FROM item, store 
LEFT JOIN stock_movement s ON s.item_id = item.id AND store.id = s.store_id
GROUP BY store.id, item.id;

I get the following error:

Error Code: 1054 Unknown column 'item.id' in 'on clause'

If I switch the query to use FROM store, item the unknown column changes:

Error Code: 1054 Unknown column 'store.id' in 'on clause'

This answer to use INNER JOIN won't work for me since there are some items and stores without corresponding stock_movement entries i.e the result is missing a few rows.

So is there a way to do this cross join?

Community
  • 1
  • 1
topher
  • 14,790
  • 7
  • 54
  • 70
  • You are already mixing implicit inner joins and an explicit left join by using the comma-separated table list in the `FROM` clause. Probably all you need is a `LEFT JOIN` between `stock_movement` and `store`, but can you post a small sample of rows from each table, together with a sample of what you expect the query output to be? – Michael Berkowski May 05 '15 at 15:29
  • Have you tried aliasing the item and store tables? – Zoldren May 05 '15 at 15:30
  • @Zoldren yes. Same error. – topher May 05 '15 at 15:32
  • 2
    The error is because of the use of both the implicit join and the explicit join, using a table from the implicit join in the explicit one's `ON` clause. – Michael Berkowski May 05 '15 at 15:34
  • @MichaelBerkowski using an explicit join worked. Please add an answer describing this further. Specifically implicit vs explicit cross joins as I can't seem to find a proper explanation online – topher May 05 '15 at 15:47
  • @topher [this question addresses it thoroughly](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins). Listing tables separated by commas in the `FROM` clause creates an `INNER JOIN`, but is an older syntax which is no longer preferred. The two are exactly equivalent. Does sgeddes' answer below give you the expected result already? I would expect that using the explicit join would produce a wildly different result than the `CROSS JOIN` below. – Michael Berkowski May 05 '15 at 15:56
  • Actually both implicit and explicit produced the same results without the join to stock_movement. The implicit broke on adding the left join. – topher May 05 '15 at 16:03
  • @MichaelBerkowski -- That is mistaken -- it isn't equivalent to an `inner join` without `where` criteria (to perform the `on`). Using commas without `where` criteria is the same as a `cross join` (cartesian product). – sgeddes May 05 '15 at 16:08
  • @sgeddes certainly. I didn't add that part because prior to your answer being accepted I wasn't certain that a cross join was what's actually needed in this instance. – Michael Berkowski May 05 '15 at 16:10
  • @MichaelBerkowski -- makes sense, just wanted to clarify. And I too wasn't 100% sure what the desired results were without sample data/desired results. – sgeddes May 05 '15 at 16:14

1 Answers1

2

You need to explicitly define your cross join:

select i.id, s.id, coalesce(sum(si.quantity),0) quantity
from store s 
  cross join item i 
  left join storeitem si on s.id = si.storeid and i.id = si.itemid
group by i.id, s.id

BTW -- by doing it this way, you are creating a cartesian product -- all stores have all items.

sgeddes
  • 62,311
  • 6
  • 61
  • 83