CODEWITHSUNDEEP
sql-server
Bernard
Bernard

Reputation: 31

Use of CASE in a SQL query

I have a SQL query like this:

SELECT * 
FROM table1
INNER JOIN table2 ON table1.key = table2.key
INNER JOIN table3 ON table1.var = table3.var
INNER JOIN table4 ON table1.field = table4.field

but I only want to include the table3 join if a variable has a certain value (iMarket=250 and above)

I'm trying to get this sort of condition working:

SELECT * 
FROM table1
INNER JOIN table2 ON table1.key = table2.key
CASE 
   WHEN iMarket > 250 
     THEN INNER JOIN table3 ON table1.var = table3.var
END
INNER JOIN table4 on table1.field = table4.field

This is pseudo-code but it approximates to what I'm trying to do. Nothing works. Is there an easier way of doing it? I want to exclude this join because it always fails if that variable is below a certain value. Thank you all!

Upvotes: 0

Views: 45

Answers (1)

dani herrera
dani herrera

Reputation: 51655

As easy to include condition in join on clause:

SELECT * from table1
INNER JOIN table2 on table1.key=table2.key
INNER JOIN table3 on iMarket>250 and table1.var=table3.var
INNER JOIN table4 on table1.field=table4.field

Remember than you should use outer joins if you need all values from other tables despite the fact that they are no matches for table3, perhaps you are looking for this behavior:

SELECT * from table1
INNER JOIN table2 on table1.key=table2.key
INNER JOIN table4 on table1.field=table4.field
left outer join   --<--
           table3 on iMarket>250 and table1.var=table3.var

Upvotes: 3

Related Questions