CODEWITHSUNDEEP
sqlsql-servert-sql
Luis Lavieri
Luis Lavieri

Reputation: 4129

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?

Upvotes: 2

Views: 71

Answers (5)

TAMIL ARASAN
TAMIL ARASAN

Reputation: 37

Solve this use for following way

SELECT T11.name as 'Profile', T12.name as 'Menu Access Group'
FROM TABLE2 AS T2
JOIN TABLE1 AS T11 ON T11.profile_id_1=T2.profile_id  
JOIN TABLE1 AS T12 ON T12.profile_id_2=T2.profile_id

I Hope this Work For Fine. try it.

Upvotes: 0

Jenism
Jenism

Reputation: 107

select  
  p1.profile_name, 
  p2.profile_name 
from profiles
  join profile p1 on p1.profile_id = profiles.profile_id_1
  join profiles p2 on p1.profile_id = profiles.profile_id_2

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14689

Try this:

DECLARE @tbl1 as TABLE(
    profile_id  INT,
    profile_name VARCHAR(50)
)

DECLARE @tbl2 as TABLE(
   Id  INT,
   profile_id_1  INT,
   profile_id_2  INT
)

INSERT INTO @tbl1 VALUES(1,'A')
INSERT INTO @tbl1 VALUES(2,'B')

INSERT INTO @tbl2 VALUES(1,1,2)
INSERT INTO @tbl2 VALUES(2,1,1)

SELECT
    T1_1.profile_name AS 'Profile',
    T1_2.profile_name AS 'Menu Access Group'
FROM @tbl2 T2
LEFT JOIN @tbl1 T1_1 ON T1_1.profile_id=T2.profile_id_1
LEFT JOIN @tbl1 T1_2 ON T1_2.profile_id=T2.profile_id_2

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You just need two INNER JOIN operations:

SELECT t2.profile_name, t3.profile_name
FROM Profile_to_menu AS t1
INNER JOIN Profile_def AS t2 ON t1.profile_id_1 = t2.profile_id
INNER JOIN Profile_def AS t3 ON t1.profile_id_1 = t3.profile_id

The first join is used to get the name of the first profile, whereas the second join is used to get the name of the second one.

Upvotes: 2

sstan
sstan

Reputation: 36523

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

Upvotes: 3

Related Questions