CODEWITHSUNDEEP
sqlselectjoin
sany
sany

Reputation: 31

SQL query to fetch unmatched records from two tables

I have two tables userprofile and deviceid.

I want to fetch username records from userprofile which are not in deviceid (username).

My query is

SELECT 
    userprofile.username, userprofile.studentname 
FROM 
    userprofile
LEFT JOIN 
    deviceid ON deviceid.username = userprofile.username
WHERE 
    deviceid.username IS NULL;

It's not fetching any values.

Upvotes: 2

Views: 18020

Answers (5)

Suganth G
Suganth G

Reputation: 5156

You are almost near to the solution

SELECT userprofile.username,userprofile.studentname FROM userprofile
  LEFT OUTER JOIN deviceid ON deviceid.username = userprofile.username
  WHERE deviceid.username IS NULL;
UNOIN ALL
SELECT userprofile.username,userprofile.studentname FROM deviceid
  LEFT OUTER JOIN userprofile ON deviceid.username = userprofile.username
  WHERE deviceid.username IS NULL;

for other type of solution Try this Fiddle

Hope this may useful

Upvotes: 0

Smart003
Smart003

Reputation: 1119

you need to fetch the values in userprofile then try the following code

select username,studentname
from userprofile 
where upper(username) not in (select upper(username) from deviceid);

the above query is in Oracle.It will returns the details of userprifile which are not in deviceid

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

In your query, you are filtering on a left joined table in your where clause. That changes the left join to an inner join. To use your approach, do your filtering in the join clause. In your specific case, all you have to do is change the word "where" to "and".

Mureinik's answer will also work, but might be slow. Hans Kilian's answer will also work.

Upvotes: 0

Hans Kilian
Hans Kilian

Reputation: 25070

This is how I usually do it

select * from userprofile a where not exists (select 1 from device b where a.username = b.username)

Upvotes: 1

Mureinik
Mureinik

Reputation: 311018

Your wording was actually the exact way to go:

I want to fetch username records from userprofile which are not in deviceid(username)

Now, we just need to put this in to SQL:

SELECT username 
FROM   userprofile
WHERE  username NOT IN (SELECT username FROM deviceid)

Upvotes: 3

Related Questions