fetch same records - SQL Server Forums

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

fetch same records

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-04-03 : 11:26:41

I need to see the records from two tables where the mobilenumbers are not same.
I have two tables named as messages and subscribers with Id, MobileNumber fields.

Now here I need to see the records where the mobile number exist in one table but not in other table.
Please give me the sql query for this.

with regards
Shaji

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-04-03 : 11:35:35

Select s.id, s. mobilenumber
from subscribers s inner join messages m on s.id = m.id
where s.mobilenumber <> m.mobilenumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 11:39:09

SELECT Id,MobileNumber
FROM Table1
WHERE MobileNumber NOT IN (SELECT MobileNumber FROM Table2)

UNION ALL

SELECT Id,MobileNumber
FROM Table2
WHERE MobileNumber NOT IN (SELECT MobileNumber FROM Table1)

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-04-03 : 11:46:39

sorry I misread the post I was running along the lines of where the two number were not the same

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-04-03 : 11:46:55

Thanks Mr.visakh16 and Mr.NeilG , Its working fine. good

Now I need to select the repeated mobilenumber from subscriber table. how to do this one?

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 12:00:40

SELECT mobilenumber FROM subscriber GROUP BY mobilenumber HAVING COUNT(*) > 1

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-04-03 : 12:01:50

no problem Mr.NeilG, but visakh16's comments is ok

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-03 : 12:06:33

Something like this should work:

SELECT Id,MobileNumber
FROM subscriber
WHERE MobileNumber IN (SELECT MobileNumber FROM subscriber group by MobileNumber having count(*)>1 )

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources