user3641053 user3641053 - 4 months ago 5
SQL Question

Database Record Has All Values

I'm using SQL Server 2012 and need some help getting the results I need in a query. My data looks like this:

ConsumerID Tag
1000028041 bBROAapp
1000028041 bBROtiva
1000028041 bClsElig
1000028041 bPCAFwd
1000028041 bTOPNoRs
1000028041 bTOPNwRq
1000028041 bTOPActv


and I want to select a given ConsumerID only if they have all of the tag values I'm looking for.

For example, this selects all records with any of the 3 tags:

SELECT ConsumerID
FROM BorrowerTags
WHERE Tag IN('bBROAapp', 'bBROtiva');


Results:

1000028041 bBROAapp
1000028041 bBROtiva


This returns no results

SELECT ConsumerID
FROM BorrowerTags
WHERE Tag = 'bBROAapp'
AND Tag = 'bBROtiva';


No Results

I only want those records where the ConsumerID has both values - not either one.

Thanks

Answer

I want to select a given ConsumerID only if they have all of the tag values

You could use:

SELECT ConsumerID 
FROM BorrowerTags 
WHERE Tag IN ('bBROAapp', 'bBROtiva')
GROUP BY ConsumerID 
HAVING COUNT(DISTINCT Tag) = 2;

LiveDemo


If you want entire records for consumer you could use:

SELECT *
FROM BorrowerTags
WHERE ConsumerID IN (... above query ...)