SQL inner join temp table only if data exists based on parameters
Hello experts -
I am trying to figure out a way to inner join to a temp table only if data exists in it. For example, a user has the option to choose 4 parameters and for each parameter a temp table is created. The end result shows only data that is present in all the temp tables. However, the user does not have to choose 4 parameters, they can choose less. If this is the case and the code uses inner joins, since temp table 4 is empty, nothing is returned. I have included my code to help (hopefully) illustrate the issue. If you run it as is, 21 rows are returned. If you comment out the last 5 SET commands (the 4th parameter) prior to the SELECT, nothing is returned as no temp table is created.
I have attached the data file as a csv.
If there is a better way to get there, please let me know.
Any help is appreciated :)
-- Lisa
I am trying to figure out a way to inner join to a temp table only if data exists in it. For example, a user has the option to choose 4 parameters and for each parameter a temp table is created. The end result shows only data that is present in all the temp tables. However, the user does not have to choose 4 parameters, they can choose less. If this is the case and the code uses inner joins, since temp table 4 is empty, nothing is returned. I have included my code to help (hopefully) illustrate the issue. If you run it as is, 21 rows are returned. If you comment out the last 5 SET commands (the 4th parameter) prior to the SELECT, nothing is returned as no temp table is created.
I have attached the data file as a csv.
If there is a better way to get there, please let me know.
Any help is appreciated :)
-- Lisa
use B045_Sappi
DECLARE @StartDate datetime, @StopDate datetime, @Origin varchar(30), @Destination varchar(30), @RouteString varchar(100)
DECLARE @EventCode1 varchar(1), @Carrier1 varchar(5), @ERPCCITY1 varchar(30), @ERPCSTATE1 varchar(2), @LESTATUS1 varchar(1),
@EventCode2 varchar(1), @Carrier2 varchar(5), @ERPCCITY2 varchar(30), @ERPCSTATE2 varchar(2), @LESTATUS2 varchar(1),
@EventCode3 varchar(1), @Carrier3 varchar(5), @ERPCCITY3 varchar(30), @ERPCSTATE3 varchar(2), @LESTATUS3 varchar(1),
@EventCode4 varchar(1), @Carrier4 varchar(5), @ERPCCITY4 varchar(30), @ERPCSTATE4 varchar(2), @LESTATUS4 varchar(1),
@EventCode5 varchar(1), @Carrier5 varchar(5), @ERPCCITY5 varchar(30), @ERPCSTATE5 varchar(2), @LESTATUS5 varchar(1),
@EventCode6 varchar(1), @Carrier6 varchar(5), @ERPCCITY6 varchar(30), @ERPCSTATE6 varchar(2), @LESTATUS6 varchar(1),
@EventCode7 varchar(1), @Carrier7 varchar(5), @ERPCCITY7 varchar(30), @ERPCSTATE7 varchar(2), @LESTATUS7 varchar(1),
@EventCode8 varchar(1), @Carrier8 varchar(5), @ERPCCITY8 varchar(30), @ERPCSTATE8 varchar(2), @LESTATUS8 varchar(1),
@EventCode9 varchar(1), @Carrier9 varchar(5), @ERPCCITY9 varchar(30), @ERPCSTATE9 varchar(2), @LESTATUS9 varchar(1),
@EventCode10 varchar(1), @Carrier10 varchar(5), @ERPCCITY10 varchar(30), @ERPCSTATE10 varchar(2), @LESTATUS10 varchar(1),
@EventCode11 varchar(1), @Carrier11 varchar(5), @ERPCCITY11 varchar(30), @ERPCSTATE11 varchar(2), @LESTATUS11 varchar(1),
@EventCode12 varchar(1), @Carrier12 varchar(5), @ERPCCITY12 varchar(30), @ERPCSTATE12 varchar(2), @LESTATUS12 varchar(1),
@EventCode13 varchar(1), @Carrier13 varchar(5), @ERPCCITY13 varchar(30), @ERPCSTATE13 varchar(2), @LESTATUS13 varchar(1),
@EventCode14 varchar(1), @Carrier14 varchar(5), @ERPCCITY14 varchar(30), @ERPCSTATE14 varchar(2), @LESTATUS14 varchar(1),
@EventCode15 varchar(1), @Carrier15 varchar(5), @ERPCCITY15 varchar(30), @ERPCSTATE15 varchar(2), @LESTATUS15 varchar(1),
@EventCode16 varchar(1), @Carrier16 varchar(5), @ERPCCITY16 varchar(30), @ERPCSTATE16 varchar(2), @LESTATUS16 varchar(1),
@EventCode17 varchar(1), @Carrier17 varchar(5), @ERPCCITY17 varchar(30), @ERPCSTATE17 varchar(2), @LESTATUS17 varchar(1),
@EventCode18 varchar(1), @Carrier18 varchar(5), @ERPCCITY18 varchar(30), @ERPCSTATE18 varchar(2), @LESTATUS18 varchar(1),
@EventCode19 varchar(1), @Carrier19 varchar(5), @ERPCCITY19 varchar(30), @ERPCSTATE19 varchar(2), @LESTATUS19 varchar(1),
@EventCode20 varchar(1), @Carrier20 varchar(5), @ERPCCITY20 varchar(30), @ERPCSTATE20 varchar(2), @LESTATUS20 varchar(1)
SET @StartDate = '07/06/2015 00:00:00'
SET @StopDate = '10/05/2015 23:59:59'
SET @Origin = 'Shawmut'
SET @Destination = 'Lebanon Jct'
SET @RouteString = 'ST-Barbers Station/CSXT'
SET @EventCode1 = 'W'
SET @Carrier1 = 'ST'
SET @ERPCCITY1 = 'Shawmut'
SET @ERPCSTATE1 = 'ME'
SET @LESTATUS1 = 'L'
SET @EventCode2 = 'A'
SET @Carrier2 = 'ST'
SET @ERPCCITY2 = 'Danville Junction'
SET @ERPCSTATE2 = 'ME'
SET @LESTATUS2 = 'L'
SET @EventCode3 = 'A'
SET @Carrier3 = 'ST'
SET @ERPCCITY3 = 'Portland'
SET @ERPCSTATE3 = 'ME'
SET @LESTATUS3 = 'L'
SET @EventCode4 = 'Z'
SET @Carrier4 = 'CSXT'
SET @ERPCCITY4 = 'Lebanon Jct'
SET @ERPCSTATE4 = 'KY'
SET @LESTATUS4 = 'L'
select
trl.*
into #segment1
from TRLSTAT trl
join SEGMENT s on trl.Track_ID = s.Track_ID
where trl.DATE_TIME_AVAIL >= @StartDate
and trl.DATE_TIME_AVAIL <= @StopDate
and ORIGINCTY = @Origin
and DESTINCTY = @Destination
and ROUTE_STRING = @RouteString
and (eventcode=@EventCode1 and carrier=@Carrier1 and erpccity=@ERPCCITY1 and erpcstate=@ERPCSTATE1 and LESTATUS = @LESTATUS1)
select
trl.*
into #segment2
from TRLSTAT trl
join SEGMENT s on trl.Track_ID = s.Track_ID
where trl.DATE_TIME_AVAIL >= @StartDate
and trl.DATE_TIME_AVAIL <= @StopDate
and ORIGINCTY = @Origin
and DESTINCTY = @Destination
and ROUTE_STRING = @RouteString
and (eventcode=@EventCode2 and carrier=@Carrier2 and erpccity=@ERPCCITY2 and erpcstate=@ERPCSTATE2 and LESTATUS = @LESTATUS2)
select
trl.*
into #segment3
from TRLSTAT trl
join SEGMENT s on trl.Track_ID = s.Track_ID
where trl.DATE_TIME_AVAIL >= @StartDate
and trl.DATE_TIME_AVAIL <= @StopDate
and ORIGINCTY = @Origin
and DESTINCTY = @Destination
and ROUTE_STRING = @RouteString
and (eventcode=@EventCode3 and carrier=@Carrier3 and erpccity=@ERPCCITY3 and erpcstate=@ERPCSTATE3 and LESTATUS = @LESTATUS3)
select
trl.*
into #segment4
from TRLSTAT trl
join SEGMENT s on trl.Track_ID = s.Track_ID
where trl.DATE_TIME_AVAIL >= @StartDate
and trl.DATE_TIME_AVAIL <= @StopDate
and ORIGINCTY = @Origin
and DESTINCTY = @Destination
and ROUTE_STRING = @RouteString
and (eventcode=@EventCode4 and carrier=@Carrier4 and erpccity=@ERPCCITY4 and erpcstate=@ERPCSTATE4 and LESTATUS = @LESTATUS4)
select
distinct s1.track_id as [Trips]
from #segment1 s1
join #segment2 s2 on s1.track_id = s2.track_id
join #segment3 s3 on s1.track_id = s3.track_id
join #segment4 s4 on s1.track_id = s4.track_id
drop table #segment1
drop table #segment2
drop table #segment3
drop table #segment4
Your last SELECT is using an INNER JOIN so will only return records if they exist. You can avoid that by using LEFT JOIN instead:
select s1.track_id as [Trips]
from #segment1 s1
left join #segment2 s2 on s1.track_id = s2.track_id
left join #segment3 s3 on s1.track_id = s3.track_id
left join #segment4 s4 on s1.track_id = s4.track_id
group by s1.track_id
By the way, you don't need any temporary table for that. Everything can be made with a single SELECT statement:
select trl.Track_ID
from TRLSTAT trl
join SEGMENT s on trl.Track_ID = s.Track_ID
where trl.DATE_TIME_AVAIL >= @StartDate
and trl.DATE_TIME_AVAIL <= @StopDate
and ORIGINCTY = @Origin
and DESTINCTY = @Destination
and ROUTE_STRING = @RouteString
and (
(eventcode=@EventCode1 and carrier=@Carrier1 and erpccity=@ERPCCITY1 and erpcstate=@ERPCSTATE1 and LESTATUS = @LESTATUS1)
or (eventcode=@EventCode2 and carrier=@Carrier2 and erpccity=@ERPCCITY2 and erpcstate=@ERPCSTATE2 and LESTATUS = @LESTATUS2)
or (eventcode=@EventCode3 and carrier=@Carrier3 and erpccity=@ERPCCITY3 and erpcstate=@ERPCSTATE3 and LESTATUS = @LESTATUS3)
or (eventcode=@EventCode4 and carrier=@Carrier4 and erpccity=@ERPCCITY4 and erpcstate=@ERPCSTATE4 and LESTATUS = @LESTATUS4)
)
group by s1.track_idASKER
When I run the above query, I get 60 results because of the 'or' statements. What I am looking for is a resulting dataset with only 21 rows since only 21 rows are common among all 3 (or all 4 if the fourth parameter is turned on) temp tables.
If you replace the last select with the below code, you will see what I mean -
I need either an inner join or an 'and' statement so I only get the common trip ids. It works if the maximum number of parameters are chosen, but returns nothing if less than the maximum number of parameters is chosen.
If you replace the last select with the below code, you will see what I mean -
select
--distinct s1.track_id as [Trips]
s1.track_id,
s2.track_id,
s3.track_id,
s4.track_id
from #segment1 s1
left join #segment2 s2 on s1.track_id = s2.track_id
left join #segment3 s3 on s1.track_id = s3.track_id
left join #segment4 s4 on s1.track_id = s4.track_idI need either an inner join or an 'and' statement so I only get the common trip ids. It works if the maximum number of parameters are chosen, but returns nothing if less than the maximum number of parameters is chosen.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did and that still gives me 60 rows returned. The problem is that I only want the rows that are common to all the temp tables, which is 21.
Below should do it in a single statement. I also fixed the @StopDate issue for you, as a bonus :-).
select
trl.track_id as [Trips]
from TRLSTAT trl
inner join SEGMENT s on trl.Track_ID = s.Track_ID
where trl.DATE_TIME_AVAIL >= @StartDate
and trl.DATE_TIME_AVAIL < DATEADD(DAY, DATEDIFF(DAY, 0, @StopDate) + 1, 0)
and ORIGINCTY = @Origin
and DESTINCTY = @Destination
and ROUTE_STRING = @RouteString
and ((eventcode=@EventCode1 and carrier=@Carrier1 and erpccity=@ERPCCITY1 and erpcstate=@ERPCSTATE1 and LESTATUS = @LESTATUS1)
or (eventcode=@EventCode2 and carrier=@Carrier2 and erpccity=@ERPCCITY2 and erpcstate=@ERPCSTATE2 and LESTATUS = @LESTATUS2)
or (eventcode=@EventCode3 and carrier=@Carrier3 and erpccity=@ERPCCITY3 and erpcstate=@ERPCSTATE3 and LESTATUS = @LESTATUS3)
or (eventcode=@EventCode4 and carrier=@Carrier4 and erpccity=@ERPCCITY4 and erpcstate=@ERPCSTATE4 and LESTATUS = @LESTATUS4))
group by trl.track_id
having
-- add up the number of matches found
MAX(case when eventcode = @Eventcode1 then 1 else 0 end) +
MAX(case when eventcode = @Eventcode2 then 1 else 0 end) +
MAX(case when eventcode = @Eventcode3 then 1 else 0 end) +
MAX(case when eventcode = @Eventcode4 then 1 else 0 end)
=
-- add up the number of values specified; if equal to number of matches, all were matched, else not
CASE WHEN @EventCode1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @EventCode2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @EventCode3 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @EventCode4 IS NULL THEN 0 ELSE 1 END
--order by track_id
select
trl.track_id as [Trips]
from TRLSTAT trl
inner join SEGMENT s on trl.Track_ID = s.Track_ID
where trl.DATE_TIME_AVAIL >= @StartDate
and trl.DATE_TIME_AVAIL < DATEADD(DAY, DATEDIFF(DAY, 0, @StopDate) + 1, 0)
and ORIGINCTY = @Origin
and DESTINCTY = @Destination
and ROUTE_STRING = @RouteString
and ((eventcode=@EventCode1 and carrier=@Carrier1 and erpccity=@ERPCCITY1 and erpcstate=@ERPCSTATE1 and LESTATUS = @LESTATUS1)
or (eventcode=@EventCode2 and carrier=@Carrier2 and erpccity=@ERPCCITY2 and erpcstate=@ERPCSTATE2 and LESTATUS = @LESTATUS2)
or (eventcode=@EventCode3 and carrier=@Carrier3 and erpccity=@ERPCCITY3 and erpcstate=@ERPCSTATE3 and LESTATUS = @LESTATUS3)
or (eventcode=@EventCode4 and carrier=@Carrier4 and erpccity=@ERPCCITY4 and erpcstate=@ERPCSTATE4 and LESTATUS = @LESTATUS4))
group by trl.track_id
having
-- add up the number of matches found
MAX(case when eventcode = @Eventcode1 then 1 else 0 end) +
MAX(case when eventcode = @Eventcode2 then 1 else 0 end) +
MAX(case when eventcode = @Eventcode3 then 1 else 0 end) +
MAX(case when eventcode = @Eventcode4 then 1 else 0 end)
=
-- add up the number of values specified; if equal to number of matches, all were matched, else not
CASE WHEN @EventCode1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @EventCode2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @EventCode3 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @EventCode4 IS NULL THEN 0 ELSE 1 END
--order by track_id
Can you post some sample data so I can see what are the difference from the query that returns 21 to the one that returns 60 records?
ASKER
Thank you for pointing me in the right direction. I ended up using a UNION and then selecting the MAX count of each identical record to get the result I wanted.
if the data will be in only one temp table.
then you can use Left Join in place of inner join.
This will be shorted way you can apply.
The records you can filter out using where clause then...