more left joins in in select

want to get names of many people

[JonB]

i have two tables: person (id,name) and company(id,worker_id,boss_id,cleaner_id).

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]

[FalseDawn]

Don't think you need left joins, try:

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

[JonB]

thank you very much FalseDawn - that was it! I will probably use LEFT JOIN since there is possibility some fields have null value and normal JOIN doesnt return that.I guess it is time for some extensive mysql learning :)