mySql join - multiple records from same table

Question

Im looping through a list of forum posts, pulling the data from 2 tables, posts and members.

I'm using a LEFT JOIN like

SELECT m.name, p.title FROM posts LEFT JOIN members m ON p.poster=m.id

That all works fine. But I also need to pull a different record from the members table, that of the p.lastposter. How would I do this? I can't add another JOIN in there can I because i'm already pulling a m.name and wouldn't it confuse it?

Answer 1

You can add another join with a different alias:

SELECT m.name, p.title, lp.name AS lastposter 
FROM posts AS p
LEFT JOIN members AS m ON p.poster=m.id
LEFT JOIN members AS lp ON p.lastposter = lp.id