joins with multiple tables and multiple rows

[mammothman42]

Hi

I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10

any help appreciated
cheers
dave

[Leif]

<ma**********@hotmail.com> skrev i en meddelelse
news:ci********@odak26.prod.google.com...

Hi

I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10

Use aliases:

select ... , firstposter´.name,lastposter.name from DiscussionThreads as
dt,users as firstposter, users as lastposter
where dt.firstpostid=firstposter.id
and dt.lastpostid=lastposter.id

You use the same table twice with two different names.

Leif

[mammothman42]

"Leif" <id****@internet.dk> wrote in message

Use aliases:

select ... , firstposter´.name,lastposter.name from DiscussionThreads as
dt,users as firstposter, users as lastposter
where dt.firstpostid=firstposter.id
and dt.lastpostid=lastposter.id

You use the same table twice with two different names.

OK, makes sense. But my resultant object, how would i fetch my
results? THat is, would $result->name return the firstname or the last
name? how do i go about giving the resultant usernames an alias?

cheers
dave

[Bill Karwin]

mammothman42 wrote:

how do i go about giving the resultant usernames an alias?

SQL allows you to assign column aliases, just as it allows you to assign
table aliases.

select firstposter.name AS firstposter_name,
lastposter.name AS lastposter_name
....

Regards,
Bill K.