LEFT AND RIGHT OUTER JOINS problem
(1) By anonymous on 2021-08-12 16:53:18 [link]
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 [link]
> Can You help me with what I am doing wrong Without the data to accompany your query, all anyone can reasonably do is speculate.
(3) By David Raymond (dvdraymond) on 2021-08-12 17:05:25 in reply to 1 [link]
> 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.
(4) By anonymous on 2021-08-12 17:12:18 in reply to 2
https://ibb.co/5cfYhvH kod do diagramu
(5) By Stephan Beal (stephan) on 2021-08-12 17:16:55 in reply to 4 [link]
> 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]
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]
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 `INSERT`s 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]
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.