Complicated JOIN .. :/

Returning one row from one table, and two rows from another table

[Warboss Alex]

Hey all,

What I want to do is this. I've got self-scripted PHP forums running quite well, but there's a lot of duplicate data around which I know is bad practice, bad me.

My revised 'topics' table has fields for topicID, topicName, timestamp, authorID and lastID, of which authorID is the ID of the user who started the topic, and lastID the ID of the user who posted to the topic last. Sample output would be something like this:

Topic ¦¦ Started By ¦¦ Last Post
-------------------------------
Topic Name ¦¦ Alex ¦¦ Bob

As is the case with most forums software these days. In this case, 'Alex' and 'Bob' are the userNames which correspond to the authorID and lastID respectively. So far I've been storing the userNames in the table, even though they're present in the 'users' table as well (bad me, like I said!), so what I want to know is how to retrieve BOTH the names from the 'users' table with a single query.

Basically, the query needs to return the topic record, and potentially two fields from two different rows from the 'users' table (the usernames aliased using something like 'users.username AS author/lastposter' whatever). I can return one of the usernames easily enough, but how do I get both? Is this even possible? Is there a better alternative? I'm open to re-design ideas.

Any help would be muchly appreciated! Thanks in advance!

Alex ...

[vincevincevince]

You need to include your users table twice:

FROM users as users1, users as users2, topics

Then you can join so that:

users1.userid = topics.lastposter AND users2.userid = topics.author

And you may select:

users1.username AS lastposter, users2.username AS author

etc..

[Warboss Alex]

SELECT t.name AS topic, sb.name AS startedBy, lp.name AS lastPoster FROM users AS sb JOIN topic AS t ON sb.userId = t.startedBy JOIN users AS lp ON t.lastPost = lp.userId

Got it working like that.. I'll try your method too though, thanks! :)