Use Joined SQL query as filter for another SQL query

filter condition in join vs where clause
sql where before join
sql join with where clause example
sql join multiple tables with conditions
sql join on multiple conditions
sql inner join
sql inner join with where clause
left join

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.

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
            )

SQL Joins Using WHERE or ON, ON filters data before tables are joined; WHERE filters after. This lesson is part of a full-length tutorial in using SQL for Data Analysis. Using Crunchbase data, let's take another look at the LEFT JOIN example from an earlier lesson Compare the following query to the previous one and you will see that everything in the  Filters, Joins, Aggregations, and All That: A Guide to Querying in SQL 14 Aug 2014. This is the fourth post in a series of posts about doing data science with SQL. The previous post went over the commands required to set up the example recipes database from the first post in this series.

SQL Inner Join to Two or More Tables, Use it to combine columns from two or more tables via a join condition. exactly the contents of one column to another, the sql inner join is called an equijoin. Below is an example of simple select statement with an INNER JOIN clause. JOIN secondtable ON join condition WHERE filter condition ORDER BY columnlist. I am trying to build a query which filters rows based on other rows as in the following example. TableA Id status type date 300 approved ACTIVE 11/12/2015 10:00:00 300 approved ACTIVE 11/12/2015 10:10:00 300 approved INACTIVE 11/12/2015 11:00:00 200 approved ACTIVE 11/12/2015 11:10:00 200 approved INACTIVE 11/12/2015 11:10:00 100 approved ACTIVE 11/12/2015 11:10:00

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      

Filter first then join or join first then filter?, Build up the query and see. The query optimizer is often smart enough to filter early. SQL is logical - in the where does not mean it will process  How to Filter Query Results In today’s lesson, you’re going to learn how to filter query results using the WHERE clause . This clause is important as only those records matching the where clause’s conditions are returned in the query results.

Inner join using the WHERE clause, How do I write two inner join queries in SQL? Is there a way to have a SQL query automatically update its' parameters based on the results of a previous query? Here's the scenario I'm trying to achieve: A report should automatically be run daily that runs the following queries (in order): SELECT id of rows that match a certain criteria (we'll call this result set "X")

Join Filters, How do I combine two select queries in SQL with different columns? WHERE: Multiple Filters with AND/OR So far, we’ve been filtering on one thing at a time. Let’s filter on a few things at once. StackOverflow Example of Multiple Filters Like you did in the last lesson, at the top of StackOverflow, click Users, then at the top, click the New Users tab, then Creation Date.

Define & modify join filter between Merge articles, A join filter allows a table to be filtered based on how a related table in the publication is filtered. These tables could be used in an application to support a mobile Set of Join Filters Between Merge Articles (SQL Server Management If you are generating join filters of five or more tables, consider other  This is certainly possible, and you're very close to the answer. All you need to do is give your inner query an alias that the outer query can use to refer to the inner query (I've called it "InnerQuery"): Select * From (SELECT ProgressStatusTypes.ProgressStatusName AS StatusName, ProgressStatusTypes.ProgressStatusID AS ID, 'Open' AS Status

Comments
  • Possible duplicate of Joining customers and tags table
  • I'm assuming that using PEG_LOG.PEG AS PEG, instead of seq_nbr would be the same logic? I will test it here in a few moments and see if this gets me what I need
  • This worked thanks! because I am using tableau and tableau does some funky stuff with sql I am not going to attempt your other response (using the with statement is funky).
  • ....this is an unusual way to do this query. If the percentage of rows with seq_nbr = 1 is small, query performance is likely to be poor (an index is unlikely to be helpful).
  • @Clockwork-Muse, would you propose an exists logic? I was starting off on that but took a turn towards CTE.
  • An EXISTS is the more usual form of this query, yes (the CTE-ness is irrelevant, as it can be done as a subquery as well). Especially as it more closely matches the intent of the question being asked.