SQL query to fetch joined result - cross join?

Question

I have table users -

id	name	email
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?

Answer 1

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.