CODEWITHSUNDEEP
mysqlsqlselectjoinleft-join
Zack Arnett
Zack Arnett

Reputation: 259

MYSQL | How to Join two tables to get the Distinct rows?

Skills:                       user_skills:

+-------------------------+   +--------------------------------+
| UID | Skill                 | UID | user_id | skill_id
---------------------------   +---------------------------------
|  1  | C++                   |  1  |    34   |     1
|  2  | C#                    |  2  |    34   |     2
|  3  | Python

My Expected output is:

+----------------------------------+
| ID(Skill ID from Skills) | Skill               
------------------------------------
| 3                        | Python

I want only the Skills that the user 34 does not have.

Current to get the skills the user does have I am using :

SELECT ss. * 
FROM skills AS s
LEFT JOIN user_skills AS us ON s.UID = us.skill_id
WHERE us.user_id = 34

But I can't seem to get the logic down to get the opposite of that. As this doesn't work when there are more that one skill per user.

SELECT s. * 
FROM skills AS s
LEFT JOIN user_skills AS us ON s.UID != us.skill_id
WHERE us.user_id = 34

Upvotes: 1

Views: 41

Answers (2)

exussum
exussum

Reputation: 18560

SELECT ss. * 
FROM skills AS s
LEFT JOIN user_skills AS us ON s.UID = us.skill_id and 
us.user =34
WHERE us.user_id is null

Your looking for when the join happens. That the user doesn't exist

So where the user is null

Upvotes: 1

Mureinik
Mureinik

Reputation: 311508

A not exists operator would be more appropriate for this requirement:

SELECT *
FROM   skills s
WHERE  NOT EXISTS (SELECT *
                   FROM   user_skills us
                   WHERE  us.skill_id = s.uid AND
                          us.user_id = 34)

Upvotes: 1

Related Questions