Solved

How to write a MS SQL query to filter data from 2 tables

Posted on 2009-07-15
4
224 Views
Last Modified: 2013-11-30
Hello All,
I am new to SQL and trying to write a select query to get a result from 2 table. Table1 is CONTACTS and 2 is USERS. CONTACTS has a field named Assigned_user_ID, which should have the same value as field ID in USERS. We actually migrated from one system to this new system, due which we have records in CONTACTS with Assigned_user_id, which are not in USERS ID. These
I want to get a list of those records which do not match in both tables. I have come up with this code, which doesn't give me any results. However, it doesn't give me any error either. Please see the code.
Many Thanks
Rafi


select * from contacts 

inner join users on contacts.assigned_user_id = users.id  

where contacts.assigned_user_id != users.id

Open in new window

0
Comment
Question by:Rafi001
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24859642
select * from contacts
left join users on contacts.assigned_user_id = users.id  
where id is null
0
 
LVL 19

Accepted Solution

by:
Limbeck earned 250 total points
ID: 24859647
select * from contacts
where not exists (select 1 from  users where contacts.assigned_user_id = users.id  )
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24859689
select * from contacts c
where c.assigned_user_id  not in  (select u.id from  users u where c.assigned_user_id = u.id  )
0
 

Author Closing Comment

by:Rafi001
ID: 31603737
Thanks Limbeck, the query worked brilliantly.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

30 Experts available now in Live!

Get 1:1 Help Now