英文:
SQL query to fetch joined result - cross join?
问题
我尝试了以下查询：
select
t3.name as "product name", t1.name as "user name", t3.price as "bought?"
from users t1
cross join products t3
left outer join cart t2
on t1.id = t2.user_id
and t3.id = t2.product_id
但是没有成功，无法得到所需的表格。有什么帮助吗？
英文:
I have table users -
| id | name | |
|---|---|---|
| 1 | tamaghna | tamaghna@g |
| 2 | arunima | arunima@g |
| 3 | rajiv | rajiv@g |
I have another table products -
| id | name | price |
|---|---|---|
| 1 | Amul Milk | 10.00 |
| 2 | Amul Butter | 20.00 |
| 3 | Amul Cheese | 30.00 |
And the final table cart
| id | user_id | product_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
What I want is a cross of two tables referencing the cart table -
| product name | user name | bought? |
|---|---|---|
| Amul milk | tamaghna | 10.00 |
| Amul Butter | tamaghna | NULL |
| Amul Cheese | tamaghna | 30.00 |
| Amul milk | arunima | 10.00 |
| Amul Butter | arunima | NULL |
| Amul Cheese | arunima | NULL |
| Amul milk | rajiv | NULL |
| Amul Butter | rajiv | NULL |
| Amul Cheese | rajiv | NULL |
I tried
select
t1.name, t1.email, t3.name, t3.price
from Users t1
left outer join cart t2
on t1.id = t2.user_id
left outer join products t3
on t2.product_id = t3.id
but no luck doesn't get the actual table out as it required. Any help?
答案1
得分: 2
你通常会使用cross join连接users和products，然后使用left join将cart表与两个引用表的列进行连接：
select u.name user_name,
p.name product_name, p.price product_price,
(c.id is not null) product_is_in_user_cart
from users u
cross join products p
left join cart c on c.user_id = u.id and c.product_id = p.id
这将为您提供一个结果集，其中包含用户名称、产品名称和价格，以及一个0/1列，指示该产品是否可以在用户的购物车中找到（这似乎比仅显示购物车中的产品价格更容易理解，但如果您愿意，仍然可以使用case来实现）。
请注意，我更改了表的别名以使它们更有意义。
英文:
You would typically cross join users and products, then bring the cart table with a left join on the columns from both reference tables:
select u.name user_name,
p.name product_name, p.price product_price,
(c.id is not null) product_is_in_user_cart
from users u
cross join products p
left join cart c on c.user_id = u.id and c.product_id = p.id
This gives you a resultset that contains the user name, the product name and price, and a 0/1 column that indicates whether that product can be found in the cart of the user (which seems more easy to understand than to display the price of products that are in cart only, but you can still get there with case if you want).
Note that I changed the table aliases to make them more meaningful.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论