question

wolfeste avatar image
wolfeste asked

Variable to Choose Inner Joins

Is there a way to have a variable to choose which joins to use? Here is a sample i put together to show what i am looking to do. Select * From Customers ( (@Var = 1 (Inner Join Locations on Customers.LocationID = Location.ID)) (@Var = 2 (INNER JOIN Shop on Customers.LocationID = Shop.ID)) )
sql-server-2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Alvin Ramard avatar image
Alvin Ramard answered
You could do something like: IF (@var = 1) BEGIN SELECT * FROM Customers INNER JOIN Locations on Customers.LocationID = Location.ID END ELSE IF (@var = 2) BEGIN SELECT * FROM Customers INNER JOIN Shop on Customers.LocationID = Shop.ID END
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Works perfect, thank you!
0 Likes 0 ·
You're welcome.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
This would do it: Select * From Customers left Join Locations on Customers.LocationID = Location.ID left JOIN Shop on Customers.LocationID = Shop.ID where (@Var = 1 and Location.ID is not null) or (@Var = 2 and Shop.ID is not null)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

wolfeste avatar image
wolfeste answered
Ok, maybe my example is to simple. Here is a more realistic example.... SELECT * --If m_ShowChargesSubmittedOnly = True FROM ((PatEncounters INNER JOIN Patients ON PatEncounters.PatientID = Patients.ID) INNER JOIN (Claims INNER JOIN (ClaimTreatments INNER JOIN PatTx ON ClaimTreatments.PatTxID = PatTx.ID) ON Claims.ID = ClaimTreatments.ClaimID) ON PatEncounters.ID = Claims.EncounterID) -------------------------------------- --If m_ShowChargesSubmittedOnly = False FROM (((PatEncounters INNER JOIN PatTx ON PatEncounters.ID = PatTx.EncounterID) INNER JOIN Patients ON PatEncounters.PatientID = Patients.ID) INNER JOIN Claims ON PatEncounters.ID = Claims.EncounterID)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I've shown you the idea of how it can be done. It's up to you really.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.