Can anyone help with SQL?

[tkbuc]

I wasn't sure if anyone on this forum had any experience with SQL and I have found this forum users to be extremely knowledgeable so I figured I would try and ask anyway.

I have a data table that lists customers and contains a row for every product the customer has. So a customer can have one row if they only have one product but can have 3 rows if they have three distinct products.

ex.

Customer Name		PRODID
Customer 1		20200B
Customer 1		20700A
Customer 1		304Z
Customer 2		20200B
Customer 2		20700A
Customer 3		304Z

I am trying to add an "AND" condition to my "Where" clause that will have the query list the customer rows where customers have product 1, 2 and 3. If a customer has only one of those products or any other combination I don't want to see them. I only want to see that combination only. In the example above, I would only expect Customer 1 to populate.

I tried writing the "AND" condition to say .... "and (P1.PRODID in (20200B,20700A) and (P1.PRODID = '304Z')) but no rows generate.

Is anyone able to assist and advise what I may be doing wrong?

[6StringJazzer]

This not really the right place to ask this but I'm not sure where to move it, unless you are using Access and I can it there.

It would help to show your entire SQL statement. I don't know what you are trying to SELECT.

You have at least two problems in what you've shown.

1. Your values in the "in" clause need to be in quotes. I am surprised this is not giving you an error.
2. The condition you are trying to write will evaluate each record. No one record can have more than one product ID, so if you specify that the record must match more than one, it can't, and you get 0 records back.

SELECT CustomerName
FROM your_table_name
WHERE PRODID IN ('20200B' , '20700A', '304Z')
GROUP BY CUSTID
HAVING COUNT(DISTINCT PRODID) = 3;

This will list the customers that have all three of the desired products.

I do not have a database set up to test this.

[tkbuc]

Thank you so much! I am going to try then and report back! I greatly appreciate your time!