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?
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
If you want 2 different rows you may use UNION:
SELECT m.name, p.title FROM posts LEFT JOIN members m ON p.poster=m.id
UNION
SELECT m.name, p.title FROM posts LEFT JOIN members m ON p.lastposter=m.id
If you want more info on the same row from lastposter do this:
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
posts
I can't add another JOIN in there can I because i'm already pulling a m.name and wouldn't it confuse it?
No.
SELECT m.name, p.title, lastpostMembers.name AS lastpostername FROM posts AS p
LEFT JOIN members m ON p.poster=m.id
LEFT JOIN members lastpostMembers ON p.lastposter=lastpostMembers.id
I really don't see why you're left joining here though; seems like you really want an inner join.
