Solved

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

Posted on 2009-07-15
4
225 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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

810 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