Need to select records with certain criteria

[nateobot]

In the table I need to be able to see all the companies with a productID of 121 or 133. Here is the kicker. They can only have those. If they have other ones then they would be excluded from this query.

Table looks like this:

CustID ProdID
143 121
143 156
143 452
144 133
145 495

So in this instance my query would only return CustID 144.

I can get it to return all the ones with 121 or 133 but getting the ones where those are their only products is stumping me.

Thanks in advance.

 

[nateobot]

Never mind. Been wracking my brain for a while now and as soon as I post it I get an idea and it works !!!

I created a table of all the custID and prodID's where those two numbers were not included. That gave me a list of all the companies that had other prodID's. Then I did a query on all the custID's that were not in that table but had a prodID of 121 or 133.

Worked like a charm.

 

[mp9]

Here's a long-winded way. I've assumed you've got a table called tblCustProd, with field CustId and ProdID, as outlined in your post.

Create five queries (these can probably be nested together, but for simplicity they're all shown here in long form).

Query0 - all records where product ID isn't 121

SELECT tblCustProd.CustID, tblCustProd.ProdID
FROM tblCustProd
WHERE tblCustProd.ProdID<>121;

Query1 - all records where product ID isn't 133

SELECT tblCustProd.CustID, tblCustProd.ProdID
FROM tblCustProd
WHERE tblCustProd.ProdID<>133;

Query2 - all records where product ID is 121

SELECT tblCustProd.CustID, tblCustProd.ProdID
FROM tblCustProd
WHERE tblCustProd.ProdID=121;

Query3 - all records where product ID is 133

SELECT tblCustProd.CustID, tblCustProd.ProdID
FROM tblCustProd
WHERE tblCustProd.ProdID=133;

Query4 - pull this all together

SELECT Query2.CustID
FROM Query2 LEFT JOIN Query0 ON Query2.CustID = Query0.CustID
WHERE Query0.ProdID Is Null
UNION SELECT Query3.CustID
FROM Query3 LEFT JOIN Query1 ON Query3.CustID = Query1.CustID
WHERE Query1.ProdID Is Null;

Hope this helps.

http://www22.brinkster.com/accessory/