CODEWITHSUNDEEP
mysql
Runar
Runar

Reputation: 245

mysql: Select from table1 rows that are missing in table2

I've been struggling with this code for a while. I copied it, and altered it from another SO-question, but I cannot get it to work.

I have a table(scores) which holds username, userID and score. and another table called userLocations, which holds userID, city, country, lat and long coordinates.

What I need to do is list every row in scores-table which doesn't have a row in the table userLocation with an identical userID.

select scores.userID , case when userLocations.userID is not null then 1 else 0 end as row_exists from scores left outer join (select distinct userID from userLocations) userLocations on scores.userID = userLocations.userID

Upvotes: 0

Views: 304

Answers (2)

jpw
jpw

Reputation: 44921

If all you want is to get a list of rows inscoresthat don't have matchinguseridinuserLocationsI believe this query should give you that with good performance:

select * from scores s
where not exists (
    select 1 from userLocations ul 
    where ul.userID = s.userID
    )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

This is your query:

select scores.userID , case when userLocations.userID is not null then 1 else 0 end as row_exists
from scores left outer join
     (select distinct userID from userLocations) userLocations
     on scores.userID = userLocations.userID;

You need a left join. I would also advocate table aliases to make the query easier to write and to read:

select s.userID , (case when ul.userID is not null then 1 else 0 end) as row_exists
from scores s left outer join
     (select distinct userID from userLocations) ul
     on s.userID = ul.userID;

If you only want rows where row_exists is 0, then add where ul.userId is NULL to the query. Actually, in this case, the best way to write the query is without the subquery:

select s.userID , (case when ul.userID is not null then 1 else 0 end) as row_exists
from scores s left outer join
     userLocations ul
     on s.userID = ul.userID
where ul.userId is NULL;

Upvotes: 2

Related Questions