CODEWITHSUNDEEP
sql-serverstored-procedures
user2391532
user2391532

Reputation: 13

Conditional joins in SQL Server

I need to query for a dataset in SQL Server and have one of the return columns based on either one of two subqueries. The control of which subquery is from a value of another column in the query. Some basic pseudo query language of what I'm trying to do:

select col1, col2, col3,
if col3 = 1
     (select count(*) from table2 where table2.col1 = table1.col1) as count1
else
     (select count(*) from table3 where table3.col1 = table1.col1) as count1
from table1

What's the best way to accomplish this?

Upvotes: 0

Views: 79

Answers (2)

Mark Schultheiss
Mark Schultheiss

Reputation: 34168

SELECT col1, col2, col3,
    CASE WHEN col3 = 1 THEN
        (SELECT count(*) 
        FROM table2 
         WHERE table2.col1 = table1.col1)
    ELSE
        (SELECT count(*)
        FROM table3 
        WHERE table3.col1 = table1.col1)
    END  AS count1
FROM table1

Upvotes: 2

Daniel A. White
Daniel A. White

Reputation: 190945

You should declare a variable and SELECT column col3 into it.

Or you could do a CASE...WHEN block.

Upvotes: 0

Related Questions