Forum Moderators: open
table "person" ,let say, contains 100 records.
so example of record in COMPANY table would be :
1,4,24,67
2,7,68,88
When I want to print everybody in some company I want that I print their NAMES,not id numbers.
So I do left join:
SELECT *,person.name as person_name FROM `company`
LEFT JOIN person ON person.id=company.worker_id
where company.id=2;
That would return the following (suppose name returned would be John):
2,7,68,88,John
That "John" is from person_name and corresponds to worker_id 7.
Now what I want as a return is :
2,7,68,88,John,Mike,Don
or just(even better)
2,John,Mike,Don
That is all 3 person ids should be substituted with with actual names.
How to do this in ONE select sentence or one database query? Thwe problem I see is that I joined person.id on company.worker_id but I hav to join it to something and I cant join in to multpipple things. Or an I?
Any help is appreciated.
[edited by: JonB at 8:59 pm (utc) on Jan. 15, 2007]
SELECT C.id, P1.name, P2.name, P3.name FROM
(((company C JOIN person P1 on C.worker_id=P1.id)
JOIN person P2 on C.boss_id=P2.id)
JOIN person P3 on C.cleaner_id=P3.id)
WHERE C.id=2
