CODEWITHSUNDEEP
sql-servert-sqlinner-join
Nate S.
Nate S.

Reputation: 1157

If statement within an inner join

Is there a way to have an if statement determine whether a join is necessary or not?

I need this because there are multiple foreign keys in the data I querying. if any of the foreign keys are null I need to change the SQL statement. I would like to write one statement that can be aware of any null values.

This is what I would like to do...

select a.*,b.* from table1 a inner join table2 b on a.id = b.id
if a.InspectorID is not null
{inner join table3 c on a.InspectionID = c.id}
else do nothing...

Upvotes: 0

Views: 265

Answers (5)

Christian Phillips
Christian Phillips

Reputation: 18779

If I understand correctly, you could use:

select a.*,b.*,c.fields 
from table1 a inner join table2 b on a.id = b.id
left join table3 c on a.InspectionID = c.id
where a.InspectionID IS NOT NULL

Upvotes: 1

Data Masseur
Data Masseur

Reputation: 1203

try this out...

select a.*,b.* from table1 a inner join table2 b on a.id = b.id
left join table3 c on a.InspectionID = c.id
where a.InspectorID is null or a.InspectionID = c.id

Upvotes: 1

Ritesh kumar
Ritesh kumar

Reputation: 278

Use dynamic sql. For detail about dynamic sql go through: http://exacthelp.blogspot.in/2013/02/writing-dynamic-sql-queries-in-sql.html

Upvotes: -1

qujck
qujck

Reputation: 14578

What about using union

select a.*,b.* 
from table1 a 
inner join table2 b on a.id = b.id
inner join table3 c on a.InspectionID = c.id
union all
select a.*,b.* 
from table1 a 
inner join table2 b on a.id = b.id
where a.InspectionID is null

Upvotes: 1

LuigiEdlCarno
LuigiEdlCarno

Reputation: 2415

I am not sure, if you can conditionally join tables, but the "if" statement in t-sql is calles case.

Upvotes: 1

Related Questions