Geek Answers Handbook

Join a table only if the column value is true

I am using SQL Server 2005. I am trying to join two tables together, but only when the 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 column value in T1 that says I need to use the values ​​in T2. I could refer to the where argument, but it will always join the table, and if the value in T1 is false, in T2 there are no values ​​to join, so the selection does not return rows.

You can't put a case around a connection, so I'm a little stuck with this ... can anyone help?

+3
source share
6 answers
select * 
from T1
join T2 
  on  T1.value = T2.value
  and T1.booleancolumn = 1
where T2.value2 = 'variable2'
and T2.value3 = 'variable3';
+3

, , , . :

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

, .

+1

, , .

, 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
0

, , booleancolumn -

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

What, if any, are performance considerations related to conditional joins, interesting? Is this the equivalent of UNION as follows:

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'
0
source

More articles:


All Articles