CODEWITHSUNDEEP
sqlsql-serverjoin
conor
conor

Reputation: 1344

SQL Conditional join using different join types and external parameter

Despite looking at related conditional join questions, I haven't been able to do the following.

I have table t with columns ID and Value, and an external parameter @Option.

If @Option = 'a' I would like to join onto table a which has columns ID and Flag, where Flag is Y/N.

If @Option = 'b' I would like to join onto table b which just has an ID column, and the existence of a matching ID indicates that Flag='Y'.

So what I want is something like

IF @Option = 'a'

SELECT 
  t.ID
  ,t.Value
  ,a.Flag
FROM t
INNER JOIN a 
  ON t.ID = a.ID

ELSE IF @Option = 'b'

SELECT 
  t.ID
  ,t.Value
  ,CASE WHEN b.ID IS NULL THEN 'N' ELSE 'Y' END AS Flag
FROM t
LEFT JOIN b 
  ON t.ID = b.ID

but I'm not sure how to set up the equivalent of the IF statements.

How can I achieve this?

One unpreferred solution is to ignore the external parameter and return two flag columns, such as:

SELECT 
  t.ID
  ,t.Value
  ,CASE WHEN a.Flag IS NULL THEN 'N' ELSE a.Flag END AS aFlag
  ,CASE WHEN b.ID IS NULL THEN 'N' ELSE 'Y' END AS bFlag
FROM t
LEFT JOIN a
  ON t.ID = a.ID 
LEFT JOIN b
  ON t.ID = b.ID

but I would really prefer to have just the one column, plus this query is slow.

Upvotes: 0

Views: 141

Answers (2)

Rokuto
Rokuto

Reputation: 814

You can also include checking value of parameter @Operation in JOIN and CASE.

SELECT 
  t.ID
  ,t.Value
  ,CASE 
      WHEN @Operation = 'a' THEN 
                              CASE WHEN a.Flag IS NULL 
                                 THEN 'N' ELSE a.Flag 
                              END 
   WHEN @Operation = 'b'THEN 
                              CASE WHEN b.ID IS NULL 
                                 THEN 'N' ELSE 'Y' 
                              END 
   END AS Flag

FROM t
LEFT JOIN a
  ON t.ID = a.ID AND @Operation = 'a'
LEFT JOIN b
  ON t.ID = b.ID AND @Operation = 'b'

In your first query, you are joining to table a with INNER JOIN, so if you want to simulate this, you can add WHERE as follow:

WHERE @Operation = 'b' OR (@Operation = 'a' AND a.Id IS NOT NULL)

Upvotes: 1

Steven
Steven

Reputation: 15283

use union

SELECT 
  t.ID
  ,t.Value
  ,a.Flag
FROM t
INNER JOIN a 
  ON t.ID = a.ID
where @Option = 'a'

union all 

SELECT 
  t.ID
  ,t.Value
  ,CASE WHEN b.ID IS NULL THEN 'N' ELSE 'Y' END AS Flag
FROM t
LEFT JOIN b 
  ON t.ID = b.ID
where @Option = 'b'

Upvotes: 2

Related Questions