asked on

SQL Query

Hi,

Need to write a query that does the following:

select F.col1, F.col2,
case when F.col3 = 1 Then XXX Else YYY End AS P
from table F
left join (select T.col3 , T.m, T.n
from tableT T
) as SecondTable ON secondTable.col3 = F.P
where ....
please note I need to compare the resultset of P (and not the F.Col3) against the T.col3

Thanks in advance

Patrick Matthews

Just repeat your CASE expression:

select F.col1, F.col2,
 case when  F.col3 = 1 Then XXX Else YYY End AS   P
from table F
left join (select T.col3 , T.m, T.n
              from tableT T
             ) as SecondTable  ON  secondTable.col3 = (case when  F.col3 = 1 Then XXX Else YYY End)
where ....

Open in new window

ASKER CERTIFIED SOLUTION

dqmq

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

awking00

Can you provide some sample data and the expected output?

Mark Wills

Interesting, you dont seem to be including anything from secondTable in the select. Guess it is just an example, and probably is something in there (if not then you have more choices)

The CTE is pretty good for this type of thing. It means the value / expression is generated once and can be reused. With this style of CTE, you can simply run as a subquery as well...

And not so sure you need a subquery for SecondTable as dgmg has also observed.

Select F.col1, F.col2, F.P, T.col3
from (select col1
           , col2
           , case 
                 when col3 = 1 Then 'XXX' Else 'YYY' 
             End AS P 
      from table ) F
left outer join tablet T on F.P = T.col3
where....

Open in new window

If you would like to post more details, then we can help decide the best approach.