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?

Please ask 1 specific researched non-duplicate question. Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. — philipxy, May 29 '23 at 07:07
"cross [join] of two tables referencing the cart table" & 1 example (with too many rows) to guess from are not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. — philipxy, May 29 '23 at 07:09

score 2 · Accepted Answer · answered May 28 '23 at 20:07

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.

SQL query to fetch joined result - cross join?

1 Answers1