[RESOLVED] Joining a table onto itself?

[errorCode30]

Hi guys,

I'm still learning mysql, and i'm having a problem understanding how to do this.

I have a table like this:

ID	Name 	MID
1	Bob	
2	Jack	
3	Amy	1
4	Lucy	2
5	Paul	1

And i'd like it to look up the names of people so the query would produce this:

1. Bob - 
2. Jack -
3. Amy - Bob
4. Lucy - Jack
5. Paul - Bob

So it looks up the name of the "MID" against the ID and Name.

Is it possible?

Thanks for the help!

[laserlight]

It would be something like:

SELECT P1.ID, P1.Name, P2.Name
FROM People P1 LEFT JOIN People P2 ON P1.Mid=P2.ID
ORDER BY P1.ID;

[errorCode30]

laserlight -- thats perfect! thanks so much!

One day i will have to master the LEFT JOIN!!! It always seems to be the answers to my sql problems.

Thanks again. 🙂

[laserlight]

Actually, in this case the act of joining a table to itself is done by means of aliases, i.e., we use P1 as an alias for People and P2 as another alias for People. The left join is so that Bob and Jack, who do not have corresponding MID entries, will still show up in the result set.