asked on 7/8/2011

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

Microsoft SQL Server 2008 SQL

Last Comment

Mark Wills

7/11/2011

Patrick Matthews

7/8/2011

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

7/8/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

awking00

7/11/2011

Can you provide some sample data and the expected output?

Mark Wills

7/11/2011

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.