CODEWITHSUNDEEP
sqlsqlite
Mark.222
Mark.222

Reputation: 103

How to query in sql from three table in one statement?

I have three tables as shown below.

How to get (in one query statement) the "product _name" that has been ordered by the user_id =1 ? (product_name for 1 ,2,3 that user_1 ordered) ?

enter image description here

Upvotes: 1

Views: 59

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Just needed a (inner)join between two tables orders and products

select p.product_name 
  from products p
  join orders o on p.product_id = o.product_id
 where o.user_id = 1

or natural join might be used

select product_name 
  from products p
  natural join orders o
 where user_id = 1

Upvotes: 1

forpas
forpas

Reputation: 164089

You need to join the 3 tables:

select u.user_name, p.*
from products p inner join orders o
on o.product_id = p.product_id
inner join users u
on u.user_id = o.user_id
where u.user_id = 1

Upvotes: 1

Eric Mamet
Eric Mamet

Reputation: 3641

Not sure to understand your question...

Maybe this could help you

SELECT product_name
FROM orders
INNER JOIN products ON products.product_id = orders.product_id
WHERE orders.user_id = 1

Note that - the users table is not required in this case since you don't care about the user name - you will get duplicate lines for the same product_name

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

Something like this would help

select * from products
where exists (
   select 1 from orders o
   inner join users u
      on o.user_id = u.user_id
   where o.product_id = u.product_id
);

Upvotes: 1

Related Questions