Use Joined SQL query as filter for another SQL query

Question

Currently I have some data that is stored in 2 tables Call_log ("A" for reference) and PEG_LOG (B for reference). In table A it stores information on calls that come into our system and gives each of them a callID. In table B it takes the Call ID and makes a new row for each step in the flow. To get all of the usable data I have to do a left join of A and B

Select 
  Call_Log.IVR_CALL_ID AS CallLogCallID,
  Call_Log.CALL_START_DTM AS CallStartTime,
  Call_Log.CALL_END_DTM AS CallEndTime,
  call_Log.XFER_GROUP AS TransferGroup,
  Call_Log.DNIS AS DNIS,
  Call_log.ANI AS ANI,
  Call_log.XFER_NBR AS TransferNumber,
  Call_Log.Caller_data_1 AS CallBackNumber,
  Call_Log.Caller_data_2 AS UserID,
  Call_Log.Caller_data_3 AS CallerData3,
  Call_Log.SERVER AS Server,
  Call_Log.PBX_ID AS PBXID,
  Call_Log.CTI_ID AS CTIID,
  Call_Log.APP AS APP,
  Call_Log.LANGUAGE AS LANGUAGE,
  Call_Log.XFER_APP AS TransferApp,
  Call_Log.XFER_PEG AS TransferPeg,
  Call_Log.XFER_STATUS AS TransferStatus,
  Call_Log.ON_DUTY_FLAG AS OnDutyFlag,
  Call_Log.CALL_HANDLED AS CallHandled,
  Call_Log.TRANSFERRED AS Transferred,
  PEG_LOG.IVR_CALL_ID AS PEGCallID,
  PEG_LOG.SEQ_NBR AS SequenceNumber,
  PEG_LOG.APP AS PEGApp,
  PEG_LOG.TRANSFERRED AS PEGTransferred,
  PEG_LOG.PEG AS PEG,
  PEG_LOG.PEG_START_DTM AS PEGStart,
  PEG_LOG.PEG_END_DTM AS PEGEnd,
  PEG_LOG.RESPONSE AS UserResponse,
  PEG_LOG.REQUEST AS Request,
  PEG_LOG.RESPONSE_STATUS AS RepsonseStatus
From Call_Log
Left join PEG_LOG ON Call_Log.IVR_CALL_ID = PEG_LOG.IVR_CALL_ID

What I need to do is get any calllogcallID that went through Peg 1 (peg_log.Peg as peg) and then pull all of the data using those calllogids as a filter

So if call 1 went through peg 1 2 3 4 5 and call 2 went through peg 2 3 4 5 and call 3 went through 1 3 4 5 then I would get all of the data for call 1 and 3 but not for call 2.

I cannot just do a filter based on any call logs that went through peg 1 because I need the information stored in the additional columns for the other pegs that call went through.

This is really out of my comfort zone for SQL and I haven't been able to find a solution to this.

Answer 1

Based on the comment to @Clockwork-Muse to my other answer, I'll present my original approach.

Honestly, it does seem better, so I may delete my other answer. Though I'm curious, if you want to test both, and let us know if this approach was in fact faster?

select      CallLogCallID = cl.ivr_call_id,
            -- other call_log columns here

            SequenceNumber = pl.seq_nbr,
            -- other peg_log columns here

from        call_log cl
left join   peg_log pl on cl.ivr_call_id = pl.ivr_call_id
where       exists (
                select  0
                from    peg_log plSub
                where   cl.ivr_call_id = plSub.ivr_call_id
                and     plSub.seq_nbr = 1
            )

Answer 2

This uses a subquery in the join for peg=1. Is this what you're after?

Declare @Call_log Table
(
IVR_CALL_ID Int,
otherfield VarChar(50)
)
Insert Into @call_log Values
(1,'xxxxx'),
(2,'yyyy'),
(3,'zzzzz'),
(4,'22222'),
(5,'333333')

Declare @Peg_Log Table
(
Id Int,
IVR_CALL_ID Int,
peg Int
)
Insert Into @Peg_log Values
(1,1,1),
(2,2,1),
(3,1,2),
(4,3,3),
(5,5,1)


Select Distinct c.IVR_CALL_ID
From 
   @Call_Log c Left join 
   @PEG_LOG p ON c.IVR_CALL_ID = p.IVR_CALL_ID INNER Join
   (Select IVR_CALL_ID From @Peg_Log Where peg =1) x ON c.IVR_CALL_ID = x.IVR_CALL_ID

Result: (Remove Distinct to get other columns)

IVR_CALL_ID
-----------
1
2
5      

Answer 3

These are the steps I would take:

• Make a case statement determining whether the seq_nbr in the peg_log table is 1 or not. If so, output 1, if not, output 0.
• Wrap this case statement in a 'max' windowed function, partitioned by the primary key on call_log (ivr_call_id?), so that it outputs '1' if ANY of our case statements hit for a grouping of the call_log primary key. Note that Windowed functions do not fold your data. So you'll still have all the individual records you had before. I called this 'hasPegSeq1'.
• Wrap all of this in a common table expression (CTE), or a subquery if you prefer.
• Call all records in the CTE that indicate that at least one row in peg_log for a call_log is 1.

Here is some code representing what was just stated:

    with

        callAndPegLogs as (

            select      CallLogCallID = cl.ivr_call_id,
                        -- other call_log columns here

                        SequenceNumber = pl.seq_nbr,
                        -- other peg_log columns here,

                        hasPegSeq1 = 
                            max(case when pl.seq_nbr = 1 then 1 else 0 end) 
                            over (partition by cl.ivr_call_id)

            from        call_log cl
            left join   peg_log pl on cl.ivr_call_id = pl.ivr_call_id

        )

        select      *
        from        callAndPegLogs cpl
        where       hasPegSeq1 = 1

Note that you can change the windowed function to 'min(pl.seq_nbr)' and get the same result, but with the case statement, you can more easily adapt to other sequence numbers if you desire.