January 19, 2008 at 9:35 am
I have two tables Tab1 and Tab2
Sample data Tab1
tab1.col1 tabl1.col2 tabl1.col3 etc...
--------------------------------------------------
USA JCrew 12000
UK Calzeat 5000
Japan GarnetHill 100
Sample data Tab2
tab2.col1 tab2.col2 tab2.col3 etc...
---------------------------------------------
USA JCrew John Doe
From these above 2 tables,
I have two results sets: (a) tab1 result set
tab1.col1 tabl1.col2
---------------------------------
USA JCrew
UK Calzeat
Japan GarnetHill
(b) not exists on tab2 result set
UK Calzeat
Japan GarnetHill
I want to construct output table
tab1.col1 tab1.col2 WorkFlag
--------------------------------
USA JCrew Y
UK Calzeat N
Japan GarnetHill N
The WorkFlag is set to Y by checking existence on tab2.
So Jcrew in on tab2 thus its WorkFlag=Y
Calzeat is not on tab2 thus its WorkFlag=N
garnetHill is not on tab2 thus its WorkFlag=N
How to code sql to get my output table? Thanks
January 19, 2008 at 10:04 am
You're looking for a left outer join (to get all rows from tab1 even where there aren't matches) and a case statement to construct the WorkFlag depending on the existence of a row in tab2
[font="Courier New"]SELECT tab1.col1, tab1.col2,
CASE WHEN tab2.col1 IS NULL THEN 'N' ELSE 'Y' END AS WorkFlag
FROM tab1 LEFT OUTER JOIN tab2 on tab1.col2 = tab2.col2[/font]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply