SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Stuck on Joins?

  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stuck on Joins?

    Hi all, I'm wondering if there is a better way to join all three of these queries to become one. Any ideas or suggestions would be greatly appreciated. Please see below my existing code:

    PHP Code:
    <?php
    $sql 
    mysql_query("SELECT * FROM customers WHERE customer_ref='$_GET[id]'");
        
    $sql mysql_fetch_array($sql);
            
    $prods mysql_query("SELECT * FROM order_products WHERE receipt_id = '$_GET[ref]' AND customer_id = '$_GET[id]'");
                
    $orderInfo mysql_query("SELECT * FROM orders WHERE cust_ref='$_GET[id]' AND receipt_id='$_GET[ref]'");
                    
    $orders mysql_fetch_array($orderInfo);
    ?>

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're retrieving everything from three different tables using the dreaded, evil "select star"

    how do we know how the tables are related? and whether they can actually be joined to return what you want? and which columns you really need?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey there, I know I should select only the columns I need to use, I'll make sure this happens. orders and order_products both have the same column 'receipt_id'. This is unique to each other placed. I then also need to grab the customers ref (their id). Thinking about it, the only tables I can join (I think) are orders and order_products. I'll have to query the table 'customers' separately.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    are you saying you accept an order but you don't know which customer ordered it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, yes I do know, I'm passing their ID through the browser. It's only a small inhouse system that I'm developing. I think I have managed to join all the tables but now one of my while loops does not work (though that's a separate matter).

    PHP Code:
    $sql mysql_query("SELECT order_products.* , orders.*, customers.* FROM orders LEFT JOIN order_products ON orders.receipt_id = order_products.receipt_id LEFT JOIN customers ON customers.customer_ref=order_products.customer_id WHERE orders.receipt_id='$_GET[ref]' AND orders.cust_ref='$_GET[id]'");
        
    $sql mysql_fetch_array($sql); 

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a single order can be for multiple order products, and each order_product has its own customer?

    i am afraid i completely fail to understand your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Show the design of your tables and provide sample inserts for each as well as the expected output. this will be easier for you to obtain assistance with your problem.


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
  •