Tricky query?

[MN]

Hi,
I have a table contain like:
Clientid Productid
000001 10
000001 11
000001 12
000002 10
000002 13
How can I select only record contain productid=10,11,12 only? (Here is
record #000001 select and record # 000002 is not select)
Thanks in advange, NM

 

[KARL DEWEY]

Try this --
SELECT MN_Table.Productid
FROM (MN_Table INNER JOIN MN_Table AS MN_Table_1 ON MN_Table.Productid =
MN_Table_1.Productid) INNER JOIN MN_Table AS MN_Table_2 ON MN_Table.Productid
= MN_Table_2.Productid
WHERE (((MN_Table.Clientid)="10") AND ((MN_Table_1.Clientid)="11") AND
((MN_Table_2.Clientid)="12"))
GROUP BY MN_Table.Productid;

 

[dtoney]

you must use a select query to query by example

 

[MN]

Thank all for reply,
Karl: I tried your query but there is no record.
Dtoney: I tried your suggestion but it still take out record #000002 (Which
is I do not want to include)
Again, Thanks

 

[KARL DEWEY]

If your Clientid is a number then drop the quotes.

 

[John Spencer]

SELECT CLIENTID
FROM TABLE as T1
WHERE Exists
(SELECT * FROM Table as T2 WHERE T2.ClientID = T1.ClientID AND
T2.ProductID = 10)
AND Exists
(SELECT * FROM Table as T2 WHERE T2.ClientID = T1.ClientID AND
T2.ProductID = 11)
AND Exists
(SELECT * FROM Table as T2 WHERE T2.ClientID = T1.ClientID AND
T2.ProductID = 12)

IF your table has only unique combinations of clientId and ProductID

SELECT Table.ClientID
FROM Table
WHERE ClientID in
(SELECT ClientID
FROM Table as T1
WHERE PRODUCTID in (10,11,12)
GROUP BY ClientID
HAVING Count(ClientID) = 3)

You can also do that if you don't have unique combinations, but that means
another level of nesting

SELECT T1.ClientID
FROM Table as T1
WHERE ClientID in
(SELECT T1.ClientID
FROM
(SELECT Distinct T3.ClientID, T3.ProductID
FROM Table as T3
WHERE T3.ProductID in (10,11,12) )as T1
GROUP BY T1.ClientID
HAVING Count(T1.ClientID) = 3)

 

[MN]

Karl,
uhh ! Sorry, I am wrong! ClientID is 000001 not 10,11,or 12!
I will change my query and see.
Thanks

 

[MN]

Thank you for all reply and your hard work
But I tried all of your solution but it seem to be not right?
May be I do wrong- anyway, I want is:
- Only record contain productID=(10 AND 11 AND 12) or (10 AND 11) or (10 AND
12) or (11 AND 12)
If there are a record have productid different than that (10,11,12) than we
exclude.

EX:
Clientid Productid
000001 10
000001 11
000001 12
000002 10
000002 13
000003 10
The result will be:
Clientid Productid
000001 10
000001 11
000001 12
000003 10

Best Regards,
MN

 

[John Spencer]

Fair enough. Your current description is different from what I understood.

You want clients that are only associated with productID 10, 11, and 12. If
they have an association with any other product then you want them excluded
from the result. If that is correct, then the following should do what you
want.

SELECT ClientID, ProductID
FROM YourTable LEFT JOIN
(SELECT ClientID
FROM YourTable as T1
WHERE ProductID Not In (10,11,12)) as T2
ON YourTable.ClientID = T2.ClientID
WHERE T2.ClientID is Null

Two Query solution. Save that as qOther
SELECT ClientID
FROM YourTable
WHERE ProductID Not In (10,11,12)

Now use that query and you original query in an unmatched query.
SELECT YourTable.*
FROM YourTable LEFT JOIN qOther
ON YourTable.ClientID = qOther.ClientID
WHERE qOther.ClientID is Null

If you have to have at least two of the productid
SELECT YourTable.ClientID
FROM YourTable LEFT JOIN qOther
ON YourTable.ClientID = qOther.ClientID
WHERE qOther.ClientID is Null
GROUP BY YourTable.ClientID
HAVING Count(YourTable.ClientID) > 1

 

[MN]

Hi John,
Sorry for reply late cause of I am still working with your solution. It is
working now.
Again thank you for your time-Best Regard.
MN