Mohamed Melouk Mohamed Melouk - 7 months ago 5
SQL Question

left join table name dynmicly form the main query

I have a

bills
table with
column customer_type
and
customer_id
fields.

This
customer_type
tells if the customer is in the
customers
table or in the
users
table or in the
suppliers
table.

I need to create a query with left join according to
customer_type
.

select c.* from bills b
left join ***b.customer_type*** c on c.id = b.customer_id

Answer

You could join all three with necessary condition:

select c.*, u.*, s.* from bills b 
left join customers c on c.id = b.customer_id and b.customer_type = 'customers'
left join users u on u.id = b.customer_id and b.customer_type = 'users'
left join suppliers s on s.id = b.customer_id and b.customer_type = 'suppliers'

Then you can take the data that is relevant from the result.

However if there are similar columns in these 3 tables you might want to restructure the database to only store one type of information in one place.

Comments