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

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

Open in new window

data.csv
Lisa CallahanBusiness Intelligence AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hello,

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...
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
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_id

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Lisa CallahanBusiness Intelligence AnalystAuthor Commented:
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 -

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_id

Open in new window


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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try my 2nd query?
Instead of LEFT JOIN you can use UNION:
select track_id
from #segment1
union
select track_id
from #segment2 
union
select track_id
from #segment3
union
select track_id
from #segment4

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lisa CallahanBusiness Intelligence AnalystAuthor Commented:
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.
Scott PletcherSenior DBACommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
Lisa CallahanBusiness Intelligence AnalystAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.