CODEWITHSUNDEEP
sql
mohammadreza khalifeh
mohammadreza khalifeh

Reputation: 1618

All products bought by a particular user

I have two tables: x_products

id    name    image  price
--  -------  ------  -----
1    name1    path     10
2    name2    path      8
3    name3    NULL      7
4    name4    path     10
5    name5    path      5

x_user_products

id  userId  productId
--  ------  --------- 
1     100      1     
2     100      2
3     105      1
4     105      3
5     100      5
6     102      2

how to SELECT all product of a user (for example user 100) in x_products? the result should be like this:

id    name    image  price
--  -------  ------  -----
1    name1    path     10
2    name2    path      8
5    name5    path      5

Upvotes: 3

Views: 51

Answers (2)

apomene
apomene

Reputation: 14389

You are looking for the INNER JOIN keyword which selects records that have matching values in both tables.

Inner Join

The code:

SELECT xp.*
FROM x_products xp
INNER JOIN x_user_products xup ON xp.id = xup.productId
WHERE xup.userId = 100

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You appears to want :

select xp.*
from x_products xp
where exists (select 1 
              from x_user_products xup 
              where xup.productId = xp.id and xup.userId = 100);

Upvotes: 0

Related Questions