Click here to Skip to main content
15,844,498 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

i have situation like bellow

table 1 have id and table2 have id, company id

i wrote case statement for insert a flag in a new table by joining the table1 and table2



SQL
select table1.id,
       flag= case when companyid=1 then 'c'
                  when companyid=<>1 then 'd'
             end
from table1 join table2 table1.id=table2.id



But here my problem i want a flag with both id's are matching and it is in companyid=1 and companyid<>1 AS 'both'

Thanks
Ramesh.m
Posted
Updated 11-Mar-13 3:23am
v2
Comments
vishal.shimpi 11-Mar-13 8:11am    
What exactly you want? your question is not clear.....
gvprabu 11-Mar-13 8:25am    
what you tried ..? give some sample Data

1 solution

Hi,

Try like as follows....

SQL
SELECT T1.id,
    CASE WHEN (EXISTS (SELECT 1 FROM table2 WHERE ID=T1.ID AND companyid=1) AND
               EXISTS (SELECT 1 FROM table2 WHERE ID=T1.ID AND companyid<>1) ) THEN 'Both'
        WHEN (EXISTS (SELECT 1 FROM table2 WHERE ID=T1.ID AND companyid=1)) THEN 'C'
        WHEN (EXISTS (SELECT 1 FROM table2 WHERE ID=T1.ID AND companyid<>1)) THEN 'D'
    END
FROM table1 T1

Regards
GVPrabu
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900