CODEWITHSUNDEEP
phpmysql
Brock
Brock

Reputation: 125

Return Ids that a user doesn't have

I have two database tables. One is egl_achievement and the other is egl_achievement_member. One just holds achievements, and the other holds members who have achievements. I'm trying to write a query that will return all achievements a member doesn't have. I thought I could use MINUS, but mysql doesn't support that.

SELECT egl_achievement.id as id FROM egl_achievement LEFT JOIN egl_achievement_member ON egl_achievement.id = egl_achievement_member.egl_achievement_id WHERE egl_achievement_member.member_id =57;

This will obviously return the ids that member 57 has, but how can I get the opposite?

Upvotes: 1

Views: 53

Answers (3)

Sander Visser
Sander Visser

Reputation: 4320

Right join and filter the ones without id (So those that are not defined in your A table)

SELECT * FROM `egl_achievement_member` `a` 
RIGHT JOIN `egl_achievement` `b` 
ON `a`.`achievement_id` = `b`.`id` 
WHERE `a`.`achievement_id` IS NULL

And then with the user

SELECT * FROM `egl_achievement_member` `a` 
RIGHT JOIN `egl_achievement` `b` 
ON `a`.`member_id` = 57 
AND `a`.`achievement_id` = `b`.`id` 
WHERE `a`.`achievement_id` IS NULL

Here is a pretty sweet schedule

SQL Joins

Upvotes: 0

Ford Filer
Ford Filer

Reputation: 325

You should be able to use NOT IN. This should select all distinct id's which member 57 does not have.

select distinct eql_achievement.id as id
from eql_achievement where eql_achievement.id not in
 (SELECT egl_achievement_member.eql_achievement_id as id FROM egl_achievement_member
WHERE egl_achievement_member.member_id =57;)

Upvotes: 0

Jimmy T.
Jimmy T.

Reputation: 4190

You can use a subselect which contains all achievments and then just list those which are not contained:

SELECT egl_achievement.id as id
FROM egl_achievement
WHERE egl_achievement.id NOT IN(
    SELECT egl_achievement_member.egl_achievement_id
    FROM egl_achievement_member
    WHERE egl_achievement_member.member_id =57);

Upvotes: 3

Related Questions