I have two temp tables set up. Table A consists of members and the businesses that they manage, multiple members can be associated to a single business. Table B consists of just the members, their ID’s, and the class of their business relationship (Retail, Business, or Retail and Business).
The query I need to come up with is to find out which of those members from Table B do not have a Retail relationship at all. Unfortunately a simple where clause will not suffice, because a member may have multiple relationships, i.e. John Doe has a Retail AND Business relationship, or possibly all three.
I can try SELECT * FROM B WHERE class='Business' which would pull all members who have Business relationships listed in the column, but on the flip side when I say WHERE class = 'Retail', it would bring in all those members who have a Business relationship as well. I want to exclude anyone from my count who doesn’t have a retail relationship at all, so from my example above, John Doe would not be included.
Advertisement
Answer
The relationships in Table B that are retail start with “Retail…” so we can select these using LIKE 'Retail%' then exclude them from the members we select from Table A by using NOT IN.
SELECT *
FROM TableA
WHERE MemberID NOT IN
(SELECT MemberID FROM TableB WHERE class LIKE 'Retail%')