SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Left Join WHERE

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

    Left Join WHERE

    Hi all, I'm having a few problems! I have two tables: orders - contains details of the customer such as their name, address, delivery address, receipt number, etc.

    The other table: order_products - contains the product details of their order such as the item, price, serial number, etc.

    I have created a LEFT JOIN which seems to be working, kind of anyway. I can't get the details to match the id= in the URL. Any ideas as to how this can be achieved? My query is below:

    PHP Code:
    $url $_GET['id'];
    $recall mysql_query("SELECT * FROM orders LEFT JOIN order_products ON orders.receipt_id=order_products.receipt_id AND orders.cust_ref=order_products.customer_id AND orders.cust_ref='$url'");
        
    $ready mysql_fetch_array($recall);
            echo 
    ''.$ready['del_method'].''

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    My initial thoughts are that you're going to have to sort out some of the relationships in your schema.

    Are you wanting to 'bodge' it up, or are you open to advice here?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey dude, sure thing, open to advice, only way to learn from my mistakes! Fire away bud

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Is $url the order id?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah it is

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You'll have to bear with me, SQL doesn't come naturally to me.

    I've tested the following, mainly because of the former caveat, but it can do doubt be improved.

    Let's say we have this...



    You should be able to get what you need with...

    Code MySQL:
    SELECT
        o.id,
        o.created,
        o.status,
        ol.product_id,
        ol.product_value,
        ol.product_quantity,
        c.fname,
        c.sname
    FROM
        orderline ol
    LEFT JOIN
        `order` o ON o.id = ol.order_id
    LEFT JOIN
        `customer` c ON c.id = o.customer_id
    WHERE
      o.id = 1

    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Anthony, that's done the trick perfectly! Thank you so much, you really go out of your way to help, I really do appreciate it bud. If your ever down in Devon I owe you a pint!

    It's good to understand how to do things the best way

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    can i make a comment?

    suppose you do what the title of this thread suggests...
    Code:
      FROM orderline ol
    LEFT OUTER
      JOIN `ORDER` o 
        ON o.id = ol.order_id
     WHERE o.id = 1
    the problem with this construction in general is that the WHERE condition destroys the "left-outer-joinedness" of the join

    any row of the left table which had no matching row in the right table will be thrown away, because the NULL that is entered into all the columns of the right table will never meet the WHERE condition

    hence it operates as an inner join

    that's the problem with using a WHERE clause on any columns of the right table in a left outer join

    having said all that, lets take a closer look at these particular tables

    under what circumstances would you expect to find a row in the orderlines table that had no matching row in the order table?

    never, right? (unless the app itself is broken)

    so it should actually be written as an INNER JOIN from the get-go

    make sense?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Cheers Rudy!

    So, if I'm following you correctly; we should use an INNER JOIN because a LEFT JOIN is implicitly designed to 'throw away' any non-matching rows but we'll never have any anyways?

    Thanks again Rudy.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    ... because a LEFT JOIN is implicitly designed to 'throw away' any non-matching rows but we'll never have any anyways?
    close but not quite

    a LEFT OUTER JOIN is implicitly designed to keep non-matching rows
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •