CODEWITHSUNDEEP
sqljoin
Julius
Julius

Reputation: 33

SQL - Join with multiple condition

I'm trying to join my users table with my jobs table based on a mapping table users_jobs:

Here is what the users table looks like:

 users
|--------|------------------|
|  id    |        name      |    
|--------|----------------- |
|   1    |  Ozzy Osbourne   |
|   2    |  Lemmy Kilmister |
|   3    | Ronnie James Dio |
|   4    |    Jimmy Page    |
|---------------------------|

jobs table looks like this:

    |--------|-----------------|
    |  id    |      title      |    
    |--------|-----------------|
    |   1    |  Singer         |
    |   2    |  Guitar Player  |
    |--------------------------|

And users_jobs table looks like this:

  |--------|-------------|-------------|---------------|-------------|
  |  id    |  user_id    |   job_id    |  column3      |    column4  |
  |--------|-------------|-------------|---------------|-------------|
  |   1    |      1      |     1       |     0         |      1      |
  |   2    |      2      |     1       |     1         |      0      |
  |   3    |      3      |     1       |     0         |      1      |
  |   4    |      4      |     2       |     1         |      0      |
  |----------------------|-------------|---------------|-------------|

For example, let's say the ozzy does a query. Here is what should expect:

|--------|------------------|------------|--------- |
|  id    |        name      |   column3  | column4  |
|--------|----------------- |------------|----------|
|   1    |  Ozzy Osbourne   |     0      |    1     |
|   2    |  Lemmy Kilmister |     1      |    0     |
|   3    | Ronnie James Dio |     0      |    1     |
|---------------------------|------------|----------|

Basically, he can only see the job in which he is registered (role) and the users included.

I tried to do this:

SELECT u1.*, uj1.colum3, uj1.column4 
FROM users AS u1 
  JOIN users_jobs AS uj1 ON uj1.user_id = 1 
  JOIN jobs AS j1 ON j1.id = up1.job_id
WHERE uj1.job_id = 1

Any help would be great!

Upvotes: 1

Views: 99

Answers (3)

Julius
Julius

Reputation: 33

I found a solution. Using @stackFan approach adding an EXISTS clause to make sure that the user is in.

SELECT u.id, u.column3 , u.column4 
FROM users u 
 INNER JOIN user_jobs uj on u.id = uj.user_id 
 INNER JOIN jobs j on j.id = uj.job_id 
WHERE uj.job_id = <job-ID>
AND 
EXISTS (
       SELECT *
       FROM users_jobs AS uj
       WHERE uj.job_id = <job-ID>
       AND uj.user_id = <user-ID>
       );

Upvotes: 1

stackFan
stackFan

Reputation: 1608

Looks like you need INNER JOIN Try this : 

select u.id, u.column3 , u.column4 from users u 
inner join user_jobs uj on u.id=uj.user_id 
inner join jobs j on j.id=uj.job_id 
where uj.job_id=1;

If you need by certain user_id

select u.id, u.column3 , u.column4 from users u 
inner join user_jobs uj on u.id=uj.user_id 
inner join jobs j on j.id=uj.job_id 
where uj.job_id=1
and u.id=1;

Upvotes: 2

Eric
Eric

Reputation: 3257

Try LEFT JOIN. It will display all users, whether they have job or not.

SELECT u.id, u.name, uj.colum3, uj.column4 
FROM users AS u 
LEFT JOIN users_jobs uj ON uj.user_id = u.id 
LEFT JOIN jobs j ON j.id = uj.job_id

Upvotes: 0

Related Questions