SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Selecting two values from multiple tables

    My apologies for making this post, i'm sure that this question has been answered before, but for the life of me I just don't know what keywords to use.

    Two tables, sold_items (buyer_id, seller_id) and users (id, nick).

    My objective is to loop through the sold items for a particular buyer_id, and retrieve the buyer and seller username (nick).

    The problem is that I don't get how I can select both nicknames when as far as I know, I can only join the buyer_id OR seller_id to the user table.

    This is how I would do it normally
    PHP Code:
    $query mysql_query("SELECT s.buyer_id, s.seller_id, u.nick FROM sold_items s, users u WHERE s.buyer_id = u.id");

    while (
    $row mysql_fetch_array($query))
    {
      
    $buyer_nick $row['nick'];
      
    $seller_nick mysql_result(mysql_query("SELECT nick FROM users WHERE id = ".$row['seller_id']),0);

    As you can see this creates a query inside the loop, but how could I select both nicks in a single query?
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by wheeler View Post
    My objective is to loop through the sold items for a particular buyer_id, and retrieve the buyer and seller username (nick).
    you're right, this has been asked before

    and you're right, a query inside a loop is really bad

    you simply need to join to the users table twice:
    Code:
    SELECT buyer.nick as buyer_nick
         , seller.nick as seller_nick
      FROM sold_items s
    INNER
      JOIN users as buyer
        ON buyer.id = s.buyer_id 
    INNER
      JOIN users as seller
        ON seller.id = s.seller_id 
     where s.buyer_id = 937
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cheers, believe me this clears alot of things up! keyword being INNER JOIN
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,608
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    SELECT b.nick, s.nick FROM sold_items, users b, users s WHERE b.id = buyer_id AND s.id = seller_id ORDER BY b.nick

    will retrieve all of the entries from the sold_items table and display the appropriate nicknames in their place. Adjust as required to retrieve just those you want to extract.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool thanks I never thought of this approach but it seems so obvious and so simple.
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development


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
  •