CODEWITHSUNDEEP
mysql
somejkuser
somejkuser

Reputation: 9040

Multiple joins off same table

I'm trying to connect two different types of users from the same users data table.

I have a database table with the following schema

agent_relationships

I have a table with user data:

users

What i want is a data set that has the following:

I know im doing this incorrectly, but heres what I've attempted:

This one returns the same data for both buyer and agent, which i expected

select u1.first_name as buyerfirst,u1.last_name as buyerlast,u1.first_name as agentfirst,u1.last_name as agentlast from users u1 left join agent_relationships ar1 on ar1.buyer_id=u1.id left join agent_relationships ar2 on ar2.agent_id=u1.id

This one fails

select u1.first_name as buyerfirst,u1.last_name as buyerlast,u2.first_name as agentfirst,u2.last_name as agentlast from users u1,users u2 left join agent_relationships ar1 on ar1.buyer_id=u1.id left join agent_relationships ar2 on ar2.agent_id=u2.id

Upvotes: 0

Views: 27

Answers (2)

babno
babno

Reputation: 309

select BU.first_name, BU.last_name, AU.first_name, AU.last_name 
from agent_relationships 
left join users BU on buyer_id = BU.id 
left join users AU on agent_id= AU.id

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You need two join on user .. depending from agent_relationships 

select u1.first_name as buyerfirst
    ,u1.last_name as buyerlast
    ,u2.first_name as agentfirst
    ,u2.last_name as agentlast 
from agent_relationships ar 
left join users  u1   ar.buyer_id=u1.id 
left join users  u2   ar.agent_id=u1.id

Upvotes: 0

Related Questions