To find exisisting data in two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

To find exisisting data in two tables

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-29 : 03:19:33

Hi,

I have two tables, i just want to find whether the 1st table value is available in the second table. if available then i need to mentioned as new column status as 'Yes' or 'No'.

Table 1:

From_Place from_code To_place to_code
Mumbai 1000 Delhi 2111
Kolkata 1211 Delhi 2111
Chennai 1111 Mumbai 1112

Table 2:
From_Place from_code To_place to_code
Mumbai 1000 Delhi 2111
Kolkata 1211 Delhi 2111
Chennai 1111 Mumbai 7524
Chennai 1004 Mumbai 5477
Kolkata 6544 Delhi 8788
Mumbai 1000 Delhi 2111
Kolkata 5466 Delhi 8782

Result should be like this based on the Table 1:

From_Place from_code To_place to_code Status
Mumbai 1000 Delhi 2111 Yes
Kolkata 1211 Delhi 2111 Yes
Chennai 1111 Mumbai 1112 No

Can anyone please assist to get the output like above.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 03:29:04

try this

Select t1.*,case when t2.from_place is null then 'no' else 'Yes' end from table1 as t1
left join table2 as t2 on t1.from_place=t2.from_place and t1.from_code=t2.from_code and
t1.to_place=t2.to_place and t1.to_code=t2.to_code

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-29 : 03:30:00

[code]
select t1.*, status = case when t2.From_Place is null then 'No' else 'Yes' end
from table1 t1
left join table2 t2 on t1.From_Place = t2.From_Place
and < . . . >
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources