Avatar of Sh M
Sh MFlag for United States of America

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
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Mark Wills
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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
Avatar of dqmq
dqmq
Flag of United States of America image

Blurred text
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
Avatar of awking00
awking00
Flag of United States of America image

Can you provide some sample data and the expected output?
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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.
SQL
SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo