In addition to my previous question (which received a great answer), I now need to select all users that own NONE of the selected productIDs
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 not ordered BOTH these 2 products:
2: Motor
735: Pasta
What query do I use to select all clients that have NONE of these product in the orders_lines table?
Help is much appreciated! (again)
(Yes, I do need ANY of these products as well, but I’m not to bothered to setup another topic after this one, I hope you guys are still willing to help out
I suspect it would’ve been better/easier to ask all three questions in a single topic, since it’s dealing with the exact same data set, but I could be wrong.
it just selects all clientsthat own any of those products but are cancelled out by count(*)=0, so instead of selecting all client that are not fetched by that query it selects no clients. It should do the exact opposite…
oops, i forgot to mention it has to be a LEFT OUTER JOIN
try this –
SELECT o.clientID
FROM orders AS o
LEFT OUTER
JOIN orders_lines AS i
ON i.orderID = o.ID
AND i.productID IN (1,23,163)
GROUP
BY o.clientID
HAVING COUNT(i.productID) = 0
this still doesn’t work, this part of your query assumes it would select all clients, but it doesn’t, it just selects the one that actually ordered these:
It should select all clients and cancel out the ones that ordered one of the selected IDs
You’re right. Because you said it was the same as the previous topic answer I left in WHERE, now it gives the result I’m looking for
In addition to that, I’m looking for the way to select all clients that do not own AT LEAST ONE of the selected productIDs, so they are allowed to have the Motor, but when they do not own the flowers, they still get selected.
Could you please confirm if this is indeed the correct way to select all clients that do not own at least one of the productIDs? Help is much appreciated
SELECT o.clientID
FROM orders AS o
LEFT OUTER
JOIN orders_lines AS i
ON i.orderID = o.ID
AND i.productID IN (1,23,163)
GROUP BY o.clientID
HAVING COUNT(i.productID) < 3
well i think it gave me the right results, but since things get so complicated in the actual real life software I’m just looking for a confirmation from a theoretical standpoint…
If they do not own all three, than they own at least not one of them, I actually think that:
fewer than 3 == not all
not all != none (answer 7)
not all == does not own at least one of the productIDs
Answer 7 gives me all clients that own NONE of the selected products.
I’m now looking for clients that do not own all of the selected products. Less than 3 (=amount of productIDs in the query) is actually the correct statement for this I believe. Correct me if i’m wrong.
Maybe i should have created another topic to avoid the confusion (asking 2 things).
