Sql Combining two tables based on a conditional outer join

I want to combine two datasets of clinical data.  The first dataset, Master, has two key fields: OrdKey and StopKey.  The second dataset, Orders, has three key fields; OrdKey, StopKey and Type.  Type can be "O", "R" or "S".  

The combined dataset is to be called OUTPUT.  If the Type = "S", use the STOPKEY as the  key field to combine the data from the two tables, otherwise use the ORDKEY.

I have attached a file depicting the the two source tables and the expected output table.

Can someone let me know how to write the join statement?

Thank you

Gl;en
Restraint_Orders.xlsx
GPSPOWAsked:
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.

plusone3055Commented:
use a case statement

IE

(CASE when type = 's' THEN
LEFT JOIN table1
 table2.STOPKEY  = table1.STOPKEY
-- rest of your statment here
ELSE
LEFT JOIN
 table2.ORDKEY = table1.ORDKEY
-- rest of your statment here
END)
GPSPOWAuthor Commented:
I am getting errors in the following:

from #RestOrd
case when #RestOrd.Type='S' then
left join #RestKey
#RestOrd.StopKey=#RestKey.StopKey
else
left join #RestKey
#RestOrd.OrdKey=#RestKey.OrdKey)
end

#RestOrd is the Orders table
#RestKey is the Master table

I am getting incorrect syntax at the Case statement.

Thanks

Glen
plusone3055Commented:
your missing the first bracket in the case statement

correct syntax

(case when #RestOrd.Type='S' then
left join #RestKey
#RestOrd.StopKey=#RestKey.StopKey
else
left join #RestKey
#RestOrd.OrdKey=#RestKey.OrdKey)
end

#RestOrd is the Orders table
#RestKey is the Master table
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

GPSPOWAuthor Commented:
I am now getting an error at the first  join statement.
plusone3055Commented:
(case when #RestOrd.Type='S' then
left join #RestKey
 ON #RestOrd.StopKey=#RestKey.StopKey
else
left join #RestKey
ON #RestOrd.OrdKey=#RestKey.OrdKey)
end

#RestOrd is the Orders table
#RestKey is the Master table
GPSPOWAuthor Commented:
if OBJECT_ID(N'tempdb..#Restkey') is not null
		Begin
			drop table #RestKey
		End
	Create table #RestKey(
VisitID	varchar(30)	,
StartEp	bigint	,
OrderID	varchar(60)	,
Category	varchar(10)	,
CategoryName	varchar(30)	,
OrderDateTime	datetime	,
OrderNumber	varchar(15)	,
OrderedProcedure	varchar(25)	,
OrderedProcedureName	varchar(75)	,
ServiceDateTime	datetime	,
Age	int	,
PtName	varchar(74)	,
Sex	varchar(1)	,
AccountNumber	varchar(15)	,
Status	varchar(6)	,
DIR	varchar(241)	,
CP	varchar(241)	,
INJUR	varchar(241)	,
TYPE	nvarchar(MAX)	,
BegOrdID	varchar(60)	,
BegOrdNumb	varchar(15)	,
RestrBegDate	datetime	,
StartOrdDate	datetime	,
EndOrdID	varchar(60)	,
StopPhys	varchar(75)	,
RestrEndDate	datetime	,
StopOrdDate	datetime	,
StopEp	bigint	,
BegShift	varchar(1)	,
EndShift	varchar(1)	,
NewLocationID	varchar(10)	,
NewRoomID	varchar(10)	,
StartPhys	varchar(75)	,
ERnk	bigint	,
EnteredUserName	varchar(40),
AdmitDateTime datetime,
DischargeDateTime datetime,
OrdKey varchar (61),
EpKey varchar (61),
StopKey varchar (61));

insert into #RestKey
SELECT     TOP (100) PERCENT #RestLogNew.VisitID, #RestLogNew.StartEp, #RestLogNew.OrderID, 
                      #RestLogNew.Category, #RestLogNew.CategoryName, #RestLogNew.OrderDateTime, 
                      #RestLogNew.OrderNumber, #RestLogNew.OrderedProcedure, 
                      #RestLogNew.OrderedProcedureName, #RestLogNew.ServiceDateTime, #RestLogNew.Age, 
                      #RestLogNew.PtName, #RestLogNew.Sex, #RestLogNew.AccountNumber, 
                      #RestLogNew.Status, #RestLogNew.DIR, #RestLogNew.CP, 
                      #RestLogNew.INJUR, #RestLogNew.TYPE, #RestLogNew.BegOrdID, 
                      #RestLogNew.BegOrdNumb, #RestLogNew.RestrBegDate, #RestLogNew.StartOrdDate, 
                      #RestLogNew.EndOrdID, #RestLogNew.StopPhys, #RestLogNew.RestrEndDate, 
                      #RestLogNew.StopOrdDate, #RestLogNew.StopEp, #RestLogNew.BegShift, 
                      #RestLogNew.EndShift, #RestLoc.NewLocationID, #RestLoc.NewRoomID, 
                      #RestLogNew.StartPhys, #RestLoc.ERnk, #RestLogNew.EnteredUserName,
                      dbo.BarVisits.AdmitDateTime,dbo.BarVisits.DischargeDateTime,
                       #RestLogNew.VisitID + '_' + CONVERT(char, #RestLogNew.OrderDateTime, 109) AS OrdKey,
                       #RestLogNew.VisitID + '_' + CONVERT(varchar, #RestLogNew.StartEp) as EpKey,
                       case when #RestLogNew.EndOrdID='' then '' else #RestLogNew.VisitID + '_' + CONVERT(char, #RestLogNew.StopOrdDate, 109) end AS StopKey
FROM         #RestLogNew LEFT OUTER JOIN
                      #RestLoc ON #RestLogNew.BegOrdID = #RestLoc.BegOrdID
                      left outer join
                      dbo.BarVisits on #RestLogNew.VisitID=dbo.BarVisits.VisitID
WHERE     (#RestLoc.ERnk = 1)
ORDER BY #RestLogNew.VisitID
select *
from #RestKey
where VisitID='A10000444689'
		

if OBJECT_ID(N'tempdb..#RestOrd') is not null
		Begin
			drop table #RestOrd
		End
	Create table #RestOrd(
VisitID	varchar(30)	,
AccountNumber	varchar(15)	,
OrderID	varchar(60)	,
OrderDateTime	datetime	,
OrdKey	varchar(61)	,
ORank	bigint	,
Type	varchar(1)	,
DateTime	datetime	,
SignatureID	int	,
SigDateTime	datetime	,
SignElectronic	varchar(1)	,
SignForID	varchar(10)	,
SignForName	varchar(30)	,
SignType	varchar(1),
StopKey varchar (61)	);
insert into #RestOrd
select *,case when Type='S' then VisitID+'_'+CONVERT(char,OrderDateTime,109) else '' end as StopKey
From [livedb].[dbo].[tbl_sp_Restraints_Order_Signature]
select *
from #RestOrd
where VisitID='A10000444689'

if OBJECT_ID(N'tempdb..#RestCmb') is not null
		Begin
			drop table #RestCmb
		End
	Create table #RestCmb(
VisitID	varchar(30)	,
AccountNumber	varchar(15)	,
OrderID	varchar(60)	,
OrderDateTime	datetime	,
DateTime	datetime	,
OrdKey varchar (61),
StartEp	bigint	,
EpKey varchar (61),
ORank	bigint	,
Type	varchar(1)	,
SignatureID	int	,
SigDateTime	datetime	,
SignElectronic	varchar(1)	,
SignForID	varchar(10)	,
SignForName	varchar(30)	,
SignType	varchar(1));
insert into #RestCmb


select #RestKey.VisitID,#RestKey.AccountNumber,#RestKey.OrderID,#RestKey.OrderDateTime,#RestOrd.DateTime,#RestKey.OrdKey,#RestKey.StartEp,#RestKey.EpKey,
#RestOrd.ORank,#RestOrd.Type,#RestOrd.SignatureID,#RestOrd.SigDateTime,#RestOrd.SignElectronic,#RestOrd.SignForID,
#RestOrd.SignForName,#RestOrd.SignType
from #RestOrd

(case when #RestOrd.Type='S' then
left   join #RestKey on
#RestOrd.StopKey=#RestKey.StopKey
else
left join #RestKey on
#RestOrd.OrdKey=#RestKey.OrdKey)

end

select *
from #RestCmb
where VisitID='A10000444689'

Open in new window


I am still getting the 'join' error.

I have embedded the complete code.

Thanks

Glen
MlandaTCommented:
You cannot quite make a JOIN conditional by embedding it in a CASE statement. You have to take a different approach. The JOIN must be created as shown below:
from #RestOrd
	left outer join #RestKey on (
		(#RestOrd.Type ='S' and #RestOrd.StopKey=#RestKey.StopKey) or
		((#RestKey.Type<>'S' or #RestKey.Type is null) and #RestOrd.OrdKey=#RestKey.OrdKey)
	)

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
GPSPOWAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for MlandaT's comment #a40966503
Assisted answer: 250 points for plusone3055's comment #a40966376
Assisted answer: 0 points for GPSPOW's comment #a40966387

for the following reason:

Thanks

I will keep working on my foreign keys and try to create a methodology to link my tables.

Glen
MlandaTCommented:
I provided a working solution to the problem. The SQL I provided shows how to do the required join. Author has not provided feedback on whether it has not worked or otherwise.
GPSPOWAuthor Commented:
Thanks

This solution worked perfectly.

Glen
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 2008

From novice to tech pro — start learning today.