CODEWITHSUNDEEP
mysqlsqljoinleft-join
user794624
user794624

Reputation: 367

MYSQL Query Left Join

Here is my query

select t1.*, t2.name as customer, t2.name as vendor from order t1
left join contact t2
on t2.id = t1.cid and t2.id = t1.vendid
where t1.id = 22

My problem is I want to select an order where order.id = 22 and instead of order.cid, order.vendid I want their names which exist in contact table.

Above query result with customer = vendor = NULL

Upvotes: 0

Views: 1594

Answers (2)

Enrico
Enrico

Reputation: 655

Ok. The query will result with customer=vendor=null because you are setting t2.id = t1.cid and t2.id=t1.vendid, or t2.id=t1.cid=t1.vendid. Since I guess no order has the same vendor and customer ID, your query wont work.

Try this:

SELECT t1.*, t2.name as cust, t3.name as vendor
FROM order t1
LEFT JOIN contact t2 ON (t2.id=t1.cid)
LEFT JOIN contact t3 on (t3.id=t1.vendid)
WHERE t1.id=22

This way you link twice with the contacts table, one of them to get the customer info and one to get the vendor info.

Upvotes: 3

ninesided
ninesided

Reputation: 23263

It sounds here like you actually want two separate joins to the contact table, one for the customer and one for the vendor, it also helps to make your table aliases more meaningful to avoid confusion:

SELECT o.*, c.name as customer, v.name as vendor 
FROM order o
LEFT JOIN contact c on (c.id = o.cid) 
LEFT JOIN contact v ON  (v.id = o.vendid)
WHERE o.id = 22

Upvotes: 1

Related Questions