SQLite Forum

LEFT AND RIGHT OUTER JOINS problem
Login

LEFT AND RIGHT OUTER JOINS problem

(1) By anonymous on 2021-08-12 16:53:18 [link] [source]

I am trying to display the total quantity sold and total sales for all of the sellers and the products even when they have not sold any of the products. I know that I have to use LEFT OUTER or RIGHT OUTER JOIN to join the tables together with NULL values.

SELECT se.seller_account_ref, se.seller_name, pro.product_code, pro.product_description, o.quantity AS [Total quantity sold], SUM(o.quantity)*o.price

FROM sellers se

INNER JOIN product_sellers pr ON se.seller_id = pr.seller_id

LEFT OUTER JOIN ordered_products o ON pr.seller_id = o.seller_id AND pr.product_id = o.product_id

LEFT OUTER JOIN products pro ON pr.product_id = pro.product_id

Even if I use left outer join I still have just 1 result for the query. Can You help me with what I am doing wrong, I think I don't understand the idea of the outer joins.

Thank You in advance guys

(2) By Stephan Beal (stephan) on 2021-08-12 16:57:09 in reply to 1 [source]

Can You help me with what I am doing wrong

Without the data to accompany your query, all anyone can reasonably do is speculate.

(4) By anonymous on 2021-08-12 17:12:18 in reply to 2 [link] [source]

https://ibb.co/5cfYhvH kod do diagramu

(5) By Stephan Beal (stephan) on 2021-08-12 17:16:55 in reply to 4 [link] [source]

kod do diagramu

All questions about why a given query does or does not work "really should" include sample data. A diagram of the schema is not nearly as helpful as the data. Posting a question about a query without sample data makes it far more difficult for this group's volunteers to help you, which greatly reduces the chances of getting a useful answer.

(6) By anonymous on 2021-08-12 17:35:10 in reply to 5 [link] [source]

Can You let me know how I can give a sample of data ?

(7) By Adrian Ho (lexfiend) on 2021-08-13 06:00:38 in reply to 6 [link] [source]

  1. Determine a minimum set of records from your dataset that, combined with your query, will show the problem you're describing.
  2. .dump your DB
  3. Pick out the INSERTs from [2] that cover the records from [1].
  4. Post these INSERT statements.

That way, anyone can paste all these commands directly into their own test DBs to verify your issue, and figure out what's going on.

(8) By Harald Hanche-Olsen (hanche) on 2021-08-13 07:52:21 in reply to 7 [link] [source]

Another way is to run .mode insert, then run SELECT statements to get a representative sample. Also, better run .schema and post the needed bits (avoiding any tables and views not relevant to the question). Try your best to keep the size of the resulting post down to the bare minimum.

(3) By David Raymond (dvdraymond) on 2021-08-12 17:05:25 in reply to 1 [link] [source]

SELECT ..., SUM(o.quantity)*o.price

You've got an aggregate function in there, but no GROUP BY. So it's using all the rows as your group, and treating the rest of the columns are bare columns, which it's taking from one of the rows "at random" (not actually random, but that's another discussion)

You need to either group by something for which you want info on the groups... or get rid of the aggregate functions.