CODEWITHSUNDEEP
sql-server
Raj
Raj

Reputation: 343

How to apply where condition in each inner join in SQL Server

I would like to apply conditions on each inner join like below 

 select * 
 from table1 table 
 inner join table2 t on t.column= table.column where condition
 inner join table3 tb on tb.column = table.column where condition

But error is thrown on second where condition

Incorrect syntax near the keyword 'inner'.

Very new to joins in SQL Server. Any help please?

Upvotes: 3

Views: 1450

Answers (3)

Joe Enos
Joe Enos

Reputation: 40393

If you're looking to keep your main query clean, you can use a CTE to logically filter your table, away from your main query:

;with Employees as
(
    select * from People where PersonType = 'Employee'
)
select * from ParkingSpots ps
join Employees e on ps.PersonID = e.PersonID

Upvotes: 0

S3S
S3S

Reputation: 25112

You need to put WHERE clauses at the end of the statement, but you can do this in an inner join like this:

select * from table1 table 
 inner join table2 t on t.column= table.column 
      and t.someColumn = 'SomeValue' --Here you can join on a condition
 inner join table3 tb on tb.column = t.column 
 where <condition>


--Or...

select * from table1 table 
 inner join table2 t on t.column= table.column 
 inner join table3 tb on tb.column = t.column 
where
 t.column = 'blah'
 and tb.column = 'blah2'

Upvotes: 4

juergen d
juergen d

Reputation: 204766

With an inner join all the conditions apply to the complete recordset. So you can just put them all in the where clause

select * 
from table1 table 
inner join table2 t on t.column= table.column
inner join table3 tb on tb.column = table.column 
where condition1 and condition2

But for instance when using a left join the conditions only apply to the join itself. So you can use the on clause like this

select *
from table1 table 
left join table2 t on t.column = table.column AND condition1
left join table3 tb on tb.column = table.column AND condition2

Upvotes: 4

Related Questions