Luis Lavieri Luis Lavieri - 3 months ago 9
SQL Question

T-SQL Inner Join

I have one

Profile
table that just contains a single property.

profile_id | profile_name
1 A
2 B
...


I have another table that combines two
Profiles
of the first table in a single place

id | profile_id_1 | profile_id_2
1 1 2
2 1 1
...


Now, what I would like to do is to query the second table, but showing the actual names. Something like:

Profile | Menu Access Group
A B
A A


What I have tried:

SELECT x.name as 'Profile', y.name as 'Menu Access Group'
FROM
(select * from profile_def
INNER JOIN profile_to_menu on profile_def_id = profile_id_1 ) as x,

(select * from profile_def
INNER JOIN profile_to_menu on profile_def_id = profile_id_2 ) as y


The problem with this is that is returning the
UNION
of everything.

How do I filter my current attempt to only return the distincts
ids
of the second table?

or more simple: How do I return the second table with their
Names
instead of the
ids
?

Answer

Select from profile_to_menu, and join twice on profile_def to get the names:

select p1.name as profile, p2.name as menu_access_group
  from profile_to_menu m
  join profile_def p1
    on p1.profile_def_id = m.profile_id_1
  join profile_def p2
    on p2.profile_def_id = m.profile_id_2