JOIN ON either of the two columns but not both

I have two tables (orders, agents) i’m trying to join on either of the two columns but not both. There are some records in orders that have both of these columns populated and it returns as duplicate

orders:

|id|order|agent_id|username|
|--+-----+--------+--------|
| 1| ord1|       5|   user1|
| 2| ord2|       6|   user2|
| 3| ord3|       7|   user3|

agents:

|id|agent|username|FName|LName|
|--+-----+--------+-----+-----|
| 5|agnt5|   user2|FNam5|LNam5|
| 6|agnt6|   user3|FNam6|LNam6|
| 7|agnt7|   user4|FNam7|LNam7|

I tried joining with an OR clause

select o.id, o.order, o.agen_id,o.username, a.Fname, a.LName
from orders o
left join agents a
on a.id = o.agent_id or a.username = o.username

I’m getting the following results

|id|order|agent_id|username|Fname|LName|
|--+-----+--------+--------+-----+-----|
| 1| ord1|       5|   user2|FNam5|LNam5|
| 1| ord1|       5|   user2|FNam5|LNam5|
| 2| ord2|       6|   user3|FNam6|LNam7|
| 2| ord2|       6|   user3|FNam6|LNam7|
| 3| ord3|       7|   user4|FNam5|LNam5|

Expected Results

|id|order|agent_id|username|Fname|LName|
|--+-----+--------+--------+-----+-----|
| 1| ord1|       5|   user2|FNam5|LNam5|
| 2| ord2|       6|   user3|FNam6|LNam7|
| 3| ord3|       7|   user4|FNam5|LNam5|

It looks in the case where both the agent_id and username are a match, its matching both and duplicating it my results. Is there a way to prevent the username match when the agent_id match is present.

Answer

You can left join twice, with the condition that evicts the second join if the first one matches:

select 
    o.id, 
    o.order, 
    o.agent_id,
    o.username, 
    coalesce(a1.fname, a2.fname) as fname, 
    coalesce(a1.lname, a2.lname) as lname
from orders o
left join agents a1 on a1.id = o.agent_id 
left join agents a2 on a1.id is null and a1.username = o.username

Advertisement

Answer