joins with multiple tables and multiple rows

[mammothman42@hotmail.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 DiscussionThrea ds, users WHERE
DiscussionThrea ds.FirstPostID= users.ID ORDER BY FirstPostDT DESC LIMIT
10

any help appreciated
cheers
dave

[Leif]

Re: joins with multiple tables and multiple rows


<mammothman42@h otmail.com> skrev i en meddelelse
news:cifvte$4li @odak26.prod.go ogle.com...[color=blue]
> 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 DiscussionThrea ds, users WHERE
> DiscussionThrea ds.FirstPostID= users.ID ORDER BY FirstPostDT DESC LIMIT
> 10
>[/color]

Use aliases:

select ... , firstposter´.na me,lastposter.n ame from DiscussionThrea ds as
dt,users as firstposter, users as lastposter
where dt.firstpostid= firstposter.id
and dt.lastpostid=l astposter.id

You use the same table twice with two different names.

Leif

[mammothman42]

Re: joins with multiple tables and multiple rows

"Leif" <idkadm@interne t.dk> wrote in message[color=blue]
> Use aliases:
>
> select ... , firstposter´.na me,lastposter.n ame from DiscussionThrea ds as
> dt,users as firstposter, users as lastposter
> where dt.firstpostid= firstposter.id
> and dt.lastpostid=l astposter.id
>
> You use the same table twice with two different names.
>[/color]
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]

Re: joins with multiple tables and multiple rows

mammothman42 wrote:[color=blue]
> how do i go about giving the resultant usernames an alias?[/color]

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

select firstposter.nam e AS firstposter_nam e,
lastposter.name AS lastposter_name
....

Regards,
Bill K.