Select twice from two different columns in the same row and the same table

[Udyret]

I got some tables I want to join. I know very basicly how to join tables, but I'm no good at it and I don't have much experience with it...

I have two tables:

_______________ _______________ ___
| meeting |
-----------------------------------------------------------
| meetingid | person1id | person2id | other |
-----------------------------------------------------------

______________
| people |
-------------------------
| peopleid | name |
-------------------------


I have the meetingid, and I want to select the meetingid, name of the person1id, name of the person2id and other.

If it makes any difference, it's gonna be used alongside php. :)

Thanks in advance!

[Atli]

Hey.

You can JOIN the `people` table with the `meeting` table twice; once for each person you need to join.

For example:
[code=sql]SELECT
m.meeting_id, m.other,
p1.id AS 'p1_id', p1.name AS 'p1_name',
p2.id AS 'p2_id', p2.name AS 'p2_name'
FROM meeting AS m
LEFT JOIN people AS p1
ON m.person1_id = p1.people_id
LEFT JOIN people AS p2
ON m.person2_id = p2.people_id
WHERE
m.meeting_id = 1; -- or something[/code]

P.S.
As a general rule, it is best to avoid using plural words for table names. Meaning; rather than name the table 'people', name it 'person'. That way you can avoid awkward names like 'peopleid' (Which makes little sense, really)

[Udyret]

Thanks, yeah, the names etc isn't the exact stuff that I actually use. Just something to make the example easy! But thanks to you, it now works and I've got more insight on how to write mysql queries with join.