SQL - Table join where primary key in first table is part of key in second

Question

Hi I am fairly new to SQL - so be gentle with me!

I basically have two tables that I am trying to join where the primary key in the first table is used multiple times in the second table (and forms the primary key in the second table along with another column)

It's probably best if I provide an example:

Table 1 : TravelRecords

TravelEventID  EventType  RecordLocator  OfficeID    
 0001            F          ABC123         LDN  
 0002            F          ABC234         LDN  
 0003            T          BDF232         SOU  
 0004            F          DFD890         GLA

Table 2 : TravelRecordRmks

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

For the second table the key is the combination of the TravelEventID and the RemarkNo which combined gives a unique ID.

Basically all I am trying to do is join the tables together and return the record locator for bookings that have a remark text line of LOWCOST BOOKING AND CREDIT CARD USED (so in the example above only ABC123 and DFD890 should be returned.

I've tried something along the lines of :

SELECT  TravelRecords.RecordLocator
FROM    TravelRecords INNER JOIN
        TravelRecordRmks ON TravelRecords.TravelEventID = TravelRecordRmks.TravelEventID db  
WHERE   (TravelRecordRmks.RemarkText = 'LOWCOST BOOKING') 
        and (TravelRecordRmks.RemarkText = 'CREDIT CARD USED')
ORDER BY dbo.vw_gTravelOrderEvent.RecordLocator

However - that returns nothing. This is probably really simple - but I can't get it to return the required response when I am looking for the single TravelEventID to contain both Remark Text fields.

Any help much appreciated Cheers

Answer 1

The most readable way to check this is probably a double where exists, like:

SELECT  tr.*
FROM    TravelRecords tr
WHERE EXISTS (
    SELECT * FROM TravelRecordRmks trr 
    WHERE trr.TravelEventID = tr.TravelEventID
    AND trr.RemarkText = 'LOWCOST BOOKING'
)
AND EXISTS (
    SELECT * FROM TravelRecordRmks trr 
    WHERE trr.TravelEventID = tr.TravelEventID
    AND trr.RemarkText = 'CREDIT CARD USED'
)

An alternative that might perform better, using an inner join subquery as a filter:

SELECT  tr.*
FROM    TravelRecords tr
INNER JOIN (
        SELECT TravelEventID
        FROM TravelRecordRmks trr 
        WHERE RemarkText IN ('CREDIT CARD USED','LOWCOST BOOKING')
        GROUP BY TravelEventID
        HAVING COUNT(DISTINCT RemarkText) = 2
) filter 
ON      filter.TravelEventID = tr.TravelEventID

The HAVING COUNT(DISTINCT RemarkText) = 2 makes sure that both types of remarks are found.

Answer 2

WHERE (TravelRecordRmks.RemarkText = 'LOWCOST BOOKING') and (TravelRecordRmks.RemarkText = 'CREDIT CARD USED')

It looks like you need OR instead of AND.

SQL boolean logic (OR and AND) is not interpreted the same way as everyday English.

Answer 3

Try something like this:

SELECT DISTINCT
  tr.RecordLocator
FROM
  TravelRecords tr
  INNER JOIN TravelRecordRmks rmk
    ON tr.TravelEventID = rmk.TravelEventID
WHERE
  rmk.RemarkText = 'LOWCOST BOOKING'
  OR rmk.RemarkText = 'CREDIT CARD USED'
ORDER BY
  tr.RecordLocator

Answer 4

The problem is with your AND condition. You are asking for records from TravelRecordRmks where it is true that the RemarkText field is 'LOWCOST BOOKING' and simultaneously is 'CREDIT CARD USED'.

What you want is:

   WHERE RemarkText IN ('LOWCOST BOOKING', 'CREDIT CARD USED')

which will find lines containing either value.

Answer 5

You can't use an and like that to check for values that occur on multiple rows. The best you can do is use an or and count to make sure you match the number of conditions that you want. Something like:

select
  tr.RecordLocator
from
  TravelRecords tr
  join (
    select 
      r.TravelEventID,
      count(*)
    from
      TravelRecords r
      join TravelRecordRmks rr on r.TravelEventID = rr.TravelEventID
    where
      rr.Text = 'LOWCOST BOOKING' or rr.Text = 'CREDIT CARD USED'
    group by
      r.TravelEventID
    having
      count(*) = 2   -- must match both (or have one of them twice) for a single TravelEventID
  ) x on x.TravelEventID = tr.TravelEventID
order by
  tr.RecordLocator

Answer 6

In addition to Andomar's IF EXISTS logic, another way that you could do it would be two joins to the remarks table:

SELECT
     TR.record_locator,
FROM
     Travel_Records TR
INNER JOIN Travel_Record_Remarks TRR1 ON
     TRR1.travel_event_id = TR.travel_event_id AND
     TRR1.remark_text = 'LOWCOST BOOKING'
INNER JOIN Travel_Record_Remarks TRR2 ON
     TRR2.travel_event_id = TR.travel_event_id AND
     TRR2.remark_text = 'CREDIT CARD USED'

There is a flaw with this statement though, which is that if a travel event has two remarks with the same text then you might end up getting back multiple rows with the same record locator.