SQL - Find missing data / negative search

Question

Hi - following on from my other post... I have a table that uses two fields to make a primary key...

TravelEventID  RemarkNo    Keyword       Text  
 0001            1           TVL          LOWCOST BOOKING  
 0001            2           TVL          CREDIT CARD USED  
 0001            3           PSG          COST CENTRE REQUIRED  
 0001            4           PSG          EMPLOYEE NUM REQUIRED
 0002            1           TVL          CREDIT CARD USED
 0002            2           AGT          BOOKED BY AGENT
 0002            3           AGT          CONFIRM WITH AIRLINE
 0002            4           TVL          LOWEST FARE CONFIRMED
 0002            5           TVL          NO CANCELLATION CHARGE
 0003            1           TVL          LOWCOST BOOKING
 0003            2           TVL          CARRIER : EASYJET
 0003            3           TVL          LOWEST FARE CONFIRMED
 0004            1           TVL          LOWCOST BOOKING
 0004            2           TVL          CREDIT CARD USED

I need to extract the TravelEventID where the Text does not contain LOWCOST BOOKING. I.E from the example table I would want only 0002.

I try and perform a query where Text <> 'LOWCOST BOOKING' however that obviously matches on most of the rows and as a result I get back all the TravelEventIDs.

Hope I've explained myself well! Cheers Darren

Answer 1

You can try something like this

SELECT DISTINCT TravelEventID
FROM Table WHERE TravelEventID NOT IN (
SELECT TravelEventID
FROM Table
WHERE Text = 'LOWCOST BOOKING'
)

Answer 2

SELECT DISTINCT T.TravelEventID
  FROM Table T 
 WHERE NOT EXISTS(SELECT * FROM Table T2 WHERE T.TravelEventID = T2.TravelEventID AND T2.Text = 'LOWCOST BOOKING')