mysql - how to join with dynamin column in table-CodePudding

I have three tables like the following:

calls_list (table)

id	line	rel_type	rel_id
1	3	user	2
2	10	lead	1

users (table)

id	name	phone
1	mehran	99999
2	reza	222222

leads (table)

id	title	status
1	lead_1	confirmed
2	lead_2	pending

I want to say on the calls_list table when select If the rel_type column is equal to the lead value, join the lead table But if the rel_type column is equal to the user value, join the users table

my query is :

SELECT * FROM calls_list as calls LEFT OUTER JOIN users as users ON users.id = calls.rel_id LEFT OUTER JOIN lead as lead ON lead.id = calls.rel_id

but not working

Is this possible? Can you help me

CodePudding user response：

You need to restrict each join to only those of the right type:

SELECT * 
FROM calls_list AS c 
LEFT OUTER JOIN users AS u 
  ON u.id = c.rel_id AND c.rel_type = 'user'
LEFT OUTER JOIN lead AS l 
  ON l.id = c.rel_id AND c.rel_type = 'lead'