I’m looking for a query that select all clientIDs that have ordered for example all 3 products.
Products: Tableproducts
ID
Name
Order Tableorders
ID
clientID
address etc.
Order Lines (products in order)orders_lines
orderID
productID
Clients tableclients
ID
Name
Etc.
Now let’s say I want to select the clients that have ordered these 3 product:
1: Flowers
23: Sheep
163: Marbles
What query do I use to select all clients that have ALL of these product in the orders_lines table?
You would need to do three separate sub selects and join them in
SELECT C.Name
FROM Client C
JOIN (SELECT O.ClientID
FROM Orders O
JOIN Order_Lines L ON O.ID = L.OrderID
JOIN Products P ON P.ID = L.ProductID
WHERE P.Name = 'Flowers') F ON F.ClientID = C.ClientID
JOIN (SELECT O.ClientID
FROM Orders O
JOIN Order_Lines L ON O.ID = L.OrderID
JOIN Products P ON P.ID = L.ProductID
WHERE P.Name = 'Sheep') S ON S.ClientID = C.ClientID
JOIN (SELECT O.ClientID
FROM Orders O
JOIN Order_Lines L ON O.ID = L.OrderID
JOIN Products P ON P.ID = L.ProductID
WHERE P.Name = 'Marbles') M ON M.ClientID = C.ClientID
This isn’t it I’m afraid, although I’m not sure what is, this doesn’t give me all clientIDs and selecting by name in a WHERE clause is obviously not the way to go.
This query selects all clients that have bought ANY of these three products.
SELECT `clientID` FROM
`orders_lines` INNER JOIN
`orders` ON
`orderID` = orders.ID
WHERE
`productID` IN (1,23,163)
GROUP BY clientID;
Now I need to get all clients that own ALL products.
OK, short of typos, what I gave you worked EXACTLY as you asked. It provided any client who ordered all three products, either in one order or over multiple orders.
Note: You need to add DISTINCT to the select to get rid of duplicates.
Are you sure your data matches what you’re expecting?
Why not? What’s the point of having the value table then?
The trick is using where with having to match only clients with the given number of products. In this case 3.
SELECT
o.clientID
FROM
orders o
INNER
JOIN
orders_lines i
ON
o.ID = i.orderID
WHERE
i.productID IN (1,23,163)
GROUP
BY
o.clientID
HAVING
COUNT(DISTINCT i.productID) = 3
May I ask why shortening all those table names is so common? It’s just a sidequestion but it makes it unnecesary difficult to clearly read the query in my opinion.
It’s shorthand. You can use Products. instead of P. so it’s a matter of preference. I prefer the shorter version as I find it easier to read, especially if you’re working over multiple databases and multiple schemas.
