471,075 Members | 910 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

joins with multiple tables and multiple rows

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

Jul 20 '05 #1
3 1425

<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
Jul 20 '05 #2
"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
Jul 20 '05 #3
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.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Prem | last post: by
1 post views Thread by Don | last post: by
9 posts views Thread by Ed_No_Spam_Please_Weber | last post: by
20 posts views Thread by p175 | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.