CODEWITHSUNDEEP
sqljoin
JustCasual
JustCasual

Reputation: 39

How to JOIN with one distinct column in one and multiple in the other?

Here are the two sample tables (Issues and Users):

╔═════════════╦══════════╗
║ reporter_id ║ agent_id ║
╠═════════════╬══════════╣
║ asdf        ║ jfid     ║
║ asde        ║          ║
║ djij        ║ dije     ║
╚═════════════╩══════════╝
╔══════╦════════════╦═══════════╗
║  id  ║ first_name ║ last_name ║
╠══════╬════════════╬═══════════╣
║ asdf ║ Magnus     ║ Nilsson   ║
║ sdfe ║ Thomas     ║ Keller    ║
║ dije ║ Daniel     ║ Humm      ║
╚══════╩════════════╩═══════════╝

Desired result:

╔═════════════╦══════════╦══════════════════╦═════════════════╦═════════════════════╦════════════════════╗
║ reporter_id ║ agent_id ║ agent_first_name ║ agent_last_name ║ reporter_first_name ║ reporter_last_name ║
╠═════════════╬══════════╬══════════════════╬═════════════════╬═════════════════════╬════════════════════╣
║ asdf        ║ idfj     ║ john             ║ lee             ║ david               ║ lee                ║
║ aiej        ║          ║ mike             ║ dee             ║                     ║                    ║
║ isao        ║ idje     ║ sarah            ║ lee             ║ sarah               ║ dee                ║
╚═════════════╩══════════╩══════════════════╩═════════════════╩═════════════════════╩════════════════════╝

What sort of join can handle this? For context, this would be on Redshift (postgres). I was thinking this...

select
    ??? 
from
    issues join users
        on issues.reporter_id = users.id AND issues.agent_id = users.id

I'm unclear on the select clause and I'm only marginally confident my from clause is correct.

Upvotes: 2

Views: 33

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

You need to Join the User table twice. Also you desired result data doesn't match with the table data.

SELECT I.reporter_id, 
       I.agent_id, 
       U1.first_name AS agent_first_name, 
       U1.last_name  AS agent_last_name, 
       U2.first_name AS reporter_first_name, 
       U2.last_name  AS reporter_last_name 
FROM   issues I
       INNER JOIN users U2 
               ON U2.id = I.reporter_id 
       LEFT JOIN users U1 
              ON U1.id = I.agent_id

Upvotes: 1

cha
cha

Reputation: 10411

You can join the same table twice. Just use a different alias, like this:

select
    issues.reporter_id
   ,issues.agent_id
   ,a.first_name as agent_first_name
   ,a.last_name as agent_last_name
   ,r.first_name as reporter_first_name
   ,r.last_name as reporter_last_name
from
    issues left join users r
        on issues.reporter_id = r.id 
    left join users a
        on issues.agent_id = a.id

Also, as you have null values for agent_id (and potentially for the reporter_id) you need to use left join

Upvotes: 0

Related Questions