SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining two tables, SELECTing two rows from one table, one from the other

    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 ...

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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
    Change names as apropriate.

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SwampBoogie, you rock.

    The usual methods for optimising this join apply, I'm assuming. Mind you, the userID's a primary key, so ..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •