CODEWITHSUNDEEP
sql
HappySnowman
HappySnowman

Reputation: 109

Get name from two ids by joining tables (SQL)

First table

MEMO_ID1 MEMO_ID2 UPDATED_BY
1 2 Bob

Second table

MEMO_ID1 MEMO_NAME
1 UD
2 LD

Result table I want:

MEMO_ID1 MEMO_ID2 UPDATED_BY
UD LD Bob 
SELECT u.MEMO_ID1, u.MEMO_ID2, u.UPDATED_BY
FROM USER u;

How can I join the user and memo tables to get the names of two different IDs?

Upvotes: 0

Views: 61

Answers (2)

Pavel Slepiankou
Pavel Slepiankou

Reputation: 3585

You didn't mentioned a database engine you're using so I will do all the stuff with T-SQL. Other DB engines have its own functions for pivoting data

In order to join data from table1 with table2 you need to pivot table1.

For pivoting this data you have 2 ways:

one option is using UNPIVOT

SELECT  updated_by, pvt_id
FROM (
    SELECT  memo_id1, memo_id2, updated_by
    FROM t1 
) pvt 
UNPIVOT (pvt_id FOR col_names IN (memo_id1, memo_id2)) AS unpvt

and another one is UNION data like that

SELECT  memo_id1, updated_by
FROM t1 

UNION

SELECT  memo_id2, updated_by
FROM t1

Now you can join this data with table2 and pivot result back

WITH source AS 
(
    SELECT  updated_by, pvt_id
    FROM 
    (
        SELECT  memo_id1
               ,memo_id2
               ,updated_by
        FROM t1 
    ) pvt UNPIVOT (pvt_id FOR col_names IN (memo_id1, memo_id2)) AS unpvt 
), 
r1 AS 
(
    SELECT  *
    FROM source s
    LEFT JOIN t2
    ON t2.memo_id = s.pvt_id 
)
SELECT  updated_by, [1] AS [memo1], [2] AS [memo2]
FROM 
(
    SELECT  updated_by, memo_name, memo_id
    FROM r1
) pvt 
PIVOT (MIN(memo_name) for memo_id IN ([1] , [2])) AS pvt2;

or the same with UNION and PIVOT

WITH source (m_id, updated_by) AS 
(
    SELECT  memo_id1, updated_by
    FROM t1 union
    SELECT  memo_id2, updated_by
    FROM t1 
), 
r1 AS 
(
    SELECT  *
    FROM source
    LEFT JOIN t2
    ON t2.memo_id = t1_data.m_id 
)
SELECT  updated_by,[1] AS [memo1],[2] AS [memo2]
FROM 
(
    SELECT  updated_by, memo_name, memo_id
    FROM r1 
) pvt 
PIVOT (MIN(memo_name) for memo_id IN ([1], [2])) AS pvt2;

Even a bit simpler solution if you really need to solution on the data of your example without extensibility

with source (m_id, updated_by) as (
    select memo_id1, updated_by
    from t1
    union
    select memo_id2, updated_by
    from t1
)
select s.updated_by, min(t2.memo_name) [memo1], max(t2.memo_name) [memo2]
from source s 
    LEFT JOIN t2 on t2.memo_id = s.m_id
group BY s.updated_by
;

Upvotes: 0

flyingfox
flyingfox

Reputation: 13506

try with below:

 select t2.MEMO_ID1,t2.MEMO_NAME,t11.UPDATED_BY
 from table2 t2 
  join table1 t11 on t2.MEMO_ID1=t11.MEMO_ID1
  join table1 t12 on t2.MEMO_ID1=t12.MEMO_ID2
 where t11.UPDATED_BY=t12.UPDATED_BY

Upvotes: 2

Related Questions