Join to a table only when column value is true

Question

Hi all.

I am using SQL Server 2005. I am trying to join 2 tables together, but only when a column value in the main table is true. Like this:

select * from T1
join T2 on T1.value = T2.value
where T2.value2 = 'variable2'
and T2.value3 = 'variable3'

There is a coulumn value in T1 which says if I have to use the values in T2. I could to a case around the where clause, but it will always join to the table, and if the value in T1 is false, there are no values in T2 to join to, so the select returns no rows.

You can't put a case around the join, so I am a little bit stuck with this ... can anyone help ?

Answer 1

select * 
from T1
join T2 
  on  T1.value = T2.value
  and T1.booleancolumn = 1
where T2.value2 = 'variable2'
and T2.value3 = 'variable3';

Answer 2

Perhaps I have misunderstood your question, but here's my guess.

I think you can use a case in the WHERE.

select * from T1
join T2 on T1.value = T2.value
where T2.value2 = case T1.useT2 when 'yes' then 'variable2' else T2.value2 END
and T2.value3 = case T1.useT2 when 'yes' then 'variable3' else T2.value3 END

Answer 3

Similar to what Dave posted, but I also read that to mean you want to actually substitute values in the results. In that case:

SELECT
    COALESCE(T2.Value, T1.Value) AS Value, 
    COALESCE(T2.Value2, T1.Value2) AS Value2,
    COALESCE(T2.Value3, T1.Value3) AS Value3
FROM T1
LEFT JOIN T2 ON T2.value = T1.value 
    AND T2.Value2= @Variable2 AND T2.Value3 = @Variable3

Note that I'm treating your constants as real variables, too.

Answer 4

Very similar to what Dave said, although I would put the booleancolumn check into the WHERE so something like this

SELECT * FROM T1
INNER JOIN T2 
  ON T1.Value = T2.Value
WHERE T2.Value2 = 'variable2'
AND T2.Value3 = 'variable3'
AND T1.booleancolumn = 1

Answer 5

Dave Marke is right, but more generaly...

conditional join is answer to your question, try find some resources on web for example

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

http://bytes.com/groups/ms-sql/641690-difference-condition-join-where

it is very powerful technique to make joins depending on data in tables

Answer 6

What, if any, are the performance considerations related to conditional joins, I wonder? Is it equivalent to a UNION like this:

SELECT T1.* 
  FROM T1
  WHERE T1.use_t2 = FALSE
  AND T1.value2 = 'variable2'
  AND T1.value3 = 'variable3'
UNION ALL
SELECT T2.* 
  FROM T1 JOIN T2 ON T1.value = T2.value
  WHERE T1.use_t2 = TRUE
  AND T2.value2 = 'variable2'
  AND T2.value3 = 'variable3'