CODEWITHSUNDEEP
mysql
Mike Ezzati
Mike Ezzati

Reputation: 3166

MySQL get multiple result from one table in one row

The are two tables "costs" and "contacts". Names off all sellers and buyers are in "contacts" table. With following query i retrieve the id of seller and buyer for each item but I want to get their names from "contacts" table

SELECT 
costs.id as ID,
costs.idContactPayedBy,
costs.idContactPayedTo

FROM costs

WHERE 
costs.idbuilding=286

but I want to get seller and buyers names from contacts table

SELECT 
costs.id as ID,
contacts.lastname as seller,
contacts.lastname as buyer

FROM costs , contacts

WHERE 
costs.idbuilding=286
and costs.idContactPayedBy = contacts.id
and costs.idContactPayedTo = contacts.id

so the desired result is like this 

ID  Seller   Buyer
21  jackson  Brown
29  Bush     wilson

Upvotes: 0

Views: 89

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

SELECT 
c.id as ID,
cntby.lastname as seller,
cntto.lastname as buyer

FROM costs AS c 
INNER JOIN contacts AS cntby ON c.idContactPayedBy = cntby.id
INNER JOIN contacts AS cntto ON c.idContactPayedTo = cntto.id
WHERE c.idbuilding=286

Note 1: Use INNER JOIN only if idContactPayed[By/To] columns are mandatory (NOT NULL). If these columns allows nulls then you should use LEFT OUTER JOIN. In my opinion, both columns should be mandatory.

Note 2: As a matter of style: please avoid old style joins (ANSI 86/89): FROM table1 a, table2 b WHERE <join condition>.

Upvotes: 2

Related Questions