CODEWITHSUNDEEP
mysqlsqljoin
Gabriel Santos
Gabriel Santos

Reputation: 4974

Union tables instead of intersect

I have the follow structure

user
id | name
----------
1  | Foo
2  | Bar

profile
id | name      | user_id
--------------------------
1  | Profile 1 | 1
2  | Profile 2 | 2

profile_access
id | user_id | profile_id
--------------------------
1  | 2       | 1

Expect the follow result from a query

id | name      | user_id
-----------------------------------
1  | Profile 1 | 1
2  | Profile 2 | 2
1  | Profile 1 | 2

But I do't know how to "merge" these tables. I tried:

SELECT profile.*
FROM profile profile
LEFT JOIN profile_access AS profile_access
ON (
   profile_access.profile_id = profile.id
)

Which returns

id | name      | user_id
-----------------------------------
1  | Profile 1 | 1
2  | Profile 2 | 2

And

SELECT profile.*
FROM profile profile
RIGHT JOIN profile_access AS profile_access
ON (
   profile_access.profile_id = profile.id
)

Which results

id | name      | user_id
-----------------------------------
2  | Profile 1 | 2

What is the correct way to do this query? Am I using joins wrong or expecting a impossible result with these tables?

EDIT: Expected result should be:

id | name      | user_id
-----------------------------------
1  | Profile 1 | 1
2  | Profile 2 | 2
1  | Profile 1 | 2

Upvotes: 3

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269863

It is unclear what the exact logic you want is, but the following returns the results in the question:

select p.id, p.name, p.user_id
from profile p
union all
select p.id, p.name, pa.user_id
from profile p join
     profile_access pa
     on pa.profile_id = p.id;

EDIT:

This returns:

id | name      | user_id
-----------------------------------
1  | Profile 1 | 1
2  | Profile 2 | 2
1  | Profile 1 | 2

Note that the last row has a 1 for the id instead of a 2 (as in the original expected answer). This is sensible to me, because id = 1 is only tied to profile name = 'Profile 1' in the table. But, to get the actual output:

select p.id, p.name, p.user_id
from profile p
union all
select pa.profile_id, p.name, pa.user_id
from profile p join
     profile_access pa
     on pa.user_id = p.user_id;

The reason I went with the first solution is because the sample queries all join profile and profile_access on the profile_id field and not on the user_id field.

Upvotes: 2

Related Questions