CODEWITHSUNDEEP
sqlpostgresql
jouerai
jouerai

Reputation: 135

How to query for all users that bought from a store knowing their orders

Say you have 3 tables (users, products, and orders) and these tables meet the following requirements:

What's the best way to query for all unique users when you only know the storeID?

Upvotes: 1

Views: 144

Answers (3)

iSR5
iSR5

Reputation: 3498

All methods will give you the same results

I would prefer to use this 

SELECT DISTINCT ID
FROM users u
INNER JOIN products p ON p.userID = u.ID
WHERE 
    p.storeID IN(SELECT storeID FROM orders)

In my opinion, best if you start your query with the smallest table, while using JOIN with large tables, so you can be more selective in the join part and would make your query elegant, and have some performance improvement.

you could be more selective by adding more conditions to the query where needed.

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Try this:

SELECT DISTINCT U.UserName 
FROM Orders O
INNER JOIN Products P ON O.productID=P.productID
INNER JOIN Users U ON U.UserID=O.UserID
WHERE P.StoreID=Input_StoreID

Upvotes: 0

Alexis.Rolland
Alexis.Rolland

Reputation: 6353

Could describe the columns available in your tables? Are you able to try something like this?

select distinct userID
from store_table as a
inner join order_table as b on a.storeID=b.storeID
inner join user_table as c on b.userID=c.userID
where a.storeID='xxxxx'

Upvotes: 0

Related Questions