compare two tables and find the occurance

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

General SQL Server Forums

New to SQL Server Programming

compare two tables and find the occurance

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-05-17 : 06:29:06

Hi friends,

I have a two table with following fields, table names are tbl_userinfo, tbl_Property.

tbl_userinfo fields are

user_id name
1 dhin
2 Mike
3 sam
4 Red

tbl_Property fields are

prpty_Id User_Id Address
1 1 3CostalRoad
2 1 westbengal
3 2 Loasass

what i want to do is, if tbl_info User_id occures in tbl_property, i want to display that full info abt tbl_userinfo

after comparing two tables Expected result is
user_id name
1 dhin
2 Mike

Please help me how to do this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-17 : 08:33:56

Select u.user_id,u.name from tbl_userinfo u inner join tbl_Property p
on u.user_id=p.user_id

Madhivanan

Failing to plan is Planning to fail

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-05-17 : 09:06:18

Hi ,

In the property table that user_Id may be occur more than once, but the final result i want is, if the user_id occur in property, i want to display only once full detail of user_info

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-17 : 09:15:58

Select * from tbl_userinfo u where exists(select * from tbl_Property where user_id=u.user_id)

Madhivanan

Failing to plan is Planning to fail

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-05-19 : 01:44:49

Thanks Madhi, i got the solution for that by your help.

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-05-19 : 01:49:55

Select * from tbl_userinfo u where exists(select * from tbl_Property where user_id=u.user_id)

This query used for find the existing user , and as well i want to know what are the users information is available in tal_userinfo but not in the tbl_property, how do i find out? please help me

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 02:19:52

Select * from tbl_userinfo u where not exists(select * from tbl_Property where user_id=u.user_id)

or

Select * from tbl_userinfo u
left join tbl_Property p
on p.user_id=u.user_id
where p.user_id is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources