CODEWITHSUNDEEP
sqlt-sqljoininner-join
Harsimranjit Singh
Harsimranjit Singh

Reputation: 81

Conditional Join SQL

I want to be able to inner join two tables based on the value of a column I'm selecting.

My Query:

SELECT varA, varB, MAX(varC) varC
FROM table1
INNER JOIN table2       ON varB = table2.varB
INNER JOIN @tempTable   ON varA = table1.column
INNER JOIN table3       ON varC = table3.column AND table3.column2 = 1 -- **IF NOT varA = 100**
INNER JOIN table4       ON varC = table4.column  **-- IF varA = 100**
LEFT OUTER JOIN table5  ON table2.column = table5.column AND table5.column2 = 1 AND table5.column3 = 4
GROUP BY varB, varA

So, in short INNER JOIN on table4 if value of column(varA) is not 100 else INNER JOIN on table3

Upvotes: 1

Views: 5911

Answers (3)

Preston
Preston

Reputation: 1346

Your question isn't quite clear and if this doesn't work please provide more info but I think this is your answer.

You can place a CASE statement in the join.

Updated, try this.

INNER JOIN table4 ON (CASE WHEN varA != 100 THEN 1 ELSE 0 END) = 1 AND tablekey=table4key
INNER JOIN table3 ON (CASE WHEN varA = 100 THEN 1 ELSE 0 END) = 0 AND tablekey=table3key

Upvotes: 1

Greg
Greg

Reputation: 3522

I think this will get the result you're after:

LEFT JOIN table3       ON varC = table3.column AND table3.column2 = 1 AND varA <> 100 -- **IF NOT varA = 100**
LEFT JOIN table4       ON varC = table4.column  AND varA = 100   **-- IF varA = 100**
...
WHERE (Table3.Column IS NOT NULL OR Table4.Column IS NOT NULL)

Basically allows a join to either table, but only return rows where one of those joins found a record. If you want to return the actual columns from the table as part of the select then you could use an ISNULL:

SELECT ISNULL(Table3.Column, Table4.Column) AS Column

EDIT: I should have added that you can't conditionally join to tables unless you want to use dynamic SQL. You can either join to the table or not. What I outlined above lets you do the join to both tables and then check that one of them did join (which is basically what an Inner Join does).

Upvotes: 2

Luis Casillas
Luis Casillas

Reputation: 30237

So, in short INNER JOIN on table3 if varA is not 100 else INNER JOIN on table4.

That cannot be effectively done in one query. At execution time, the database must decide what joins to perform on a whole-table basis, not a row-by-row basis. If you attempt to do what you propose, what you will get, at best, is that the database will join on both table3 and table4. At worst, the joins will be Cartesian (unrestricted "all rows * all rows" joins).

So you only have these two options:

  1. Join to both tables, and select on a row-by-row basis whether to produce data from table3 or table4 for that row.
  2. Split the query into two queries joined by UNION. One of the queries joins with table3, the other joins with table4. You use the condition to control which rows are produced by the first query and which by the second one; i.e., the subquery that joins with table3 has varA != 100 in the WHERE clause, and the subquery that joins with table4 has varA = 100.

Upvotes: 1

Related Questions