SQL Select Distinct Group By Having Two Items-CodePudding

I currently have a table with data like below.

How would I go about grouping by HeaderId and get the distinct HeaderId where it contains multiple specified items?

An example is to return which HeaderId contains NTNB and NMPTN locations. I use the SQL below and then use C# to manually check if they are the same HeaderId .

SELECT DISTINCT([HeaderId]) FROM [dbo].[timings] WHERE Location = 'NTNB'
SELECT DISTINCT([HeaderId]) FROM [dbo].[timings] WHERE Location = 'NMPTN'

An expected outcome looking for distinct HeaderId's containing NTNB and NMPTN for the data below would be 4.

HeaderId    Ordinal Location
3   0   KRKYLEJ
3   1   IRNVLJN
3   2   LGML
3   3   TRWLJN
3   4   STAPLFD
3   5   TOTODSL
4   0   CREWBHM
4   1   CREWBHJ
4   2   MADELEY
4   3   NTNB
4   4   STAFFRD
4   5   STAFTVJ
4   6   WHHSJN
4   7   COLWICH
4   8   RUGLYNJ
4   9   RUGL
4   10  LCHTNJ
4   11  AMNGTNJ
4   12  NNTN
4   13  RUGBTVJ
4   14  RUGBY
4   15  HMTNJ
4   16  LNGBKBY
4   17  NMPTN
4   18  HANSLPJ
4   19  MKNSCEN
4   20  DNBGHSJ
4   21  BLTCHLY
4   22  LEDBRNJ
4   23  TRING
4   24  BONENDJ
4   25  WATFDJ
4   26  HROW
4   27  WMBY
4   28  WLSDNBJ
4   29  HARLSJN
4   30  WLSDWLJ
4   31  CMDNJN
4   32  CMDNSTH
4   33  EUSTON
4   34  CMDNSTH
4   35  CMDNJN
4   36  QPRKAC

CodePudding user response：

Aggregate by the HeaderId and then assert that both locations are present:

SELECT HeaderId
FROM timings
WHERE Location IN ('NTNB', 'NMPTN')
GROUP BY HeaderId
HAVING MIN(Location) <> MAX(Location)