CODEWITHSUNDEEP
sql-serversql-server-2012
Raymond Morphy
Raymond Morphy

Reputation: 2526

How to select IDs from second table which all IDs exists in first table?

I have following tables:

TravelPage

TravelPageID     TravelIDRef     PassengerIDRef
    1                1                10
    2                1                15
    3                2                10

Temp

PassengerID
   10
   15

I want to get all travelIDRefs from TravelPage Table which all PassegerIDs(10,15) from temp table exists in travelPage table

Result should be

travelIDRef=1 because both 10,15 exists just for TravelIDRef=1

declare @TravelPage table
(
   TravelPageID int,
   TravelIdRef int,
   PassengerIDRef int
)
declare @temp table
(
   PassengerID int
)


insert into @TravelPage
  values(1,1,10),(2,1,15),(3,2,10)

insert into @temp
values(10),(15)

Upvotes: 1

Views: 52

Answers (1)

sagi
sagi

Reputation: 40481

Use the HAVING clause :

SELECT t.TravelIdRef
FROM TravelPage t
CROSS JOIN Temp p
GROUP BY TravelIdRef
HAVING COUNT(DISTINCT t.PassengerIDRef) = COUNT(DISTINCT p.PassengerID)

Or with a sub query instead of a join :

SELECT t.TravelIdRef
FROM TravelPage t
GROUP BY TravelIdRef
HAVING COUNT(DISTINCT t.PassengerIDRef) = 
       (SELECT COUNT(DISTINCT p.PassengerID) FROM Temp p)

Upvotes: 1

Related Questions