Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: Left join query

  1. #1
    New Coder
    Join Date
    Aug 2002
    Posts
    86
    Thanks
    2
    Thanked 1 Time in 1 Post

    Left join query

    Hi Guys,

    Im having trouble getting a query to work.

    I need it to get all the fields from table 'order_product' with the where clause and with the results also all the details from table 'product' where the product_id fields match.

    This is what I have so far but am stuck now as its not working :

    Code:
    $order_product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "' LEFT JOIN product "."ON product_id = order_product.product_id");

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    You have to use legal SQL syntax.

    You can NEVER have the WHERE clause before the JOIN.
    Code:
    $sql = "SELECT * FROM " . DB_PREFIX . "order_product "
         . " LEFT JOIN product ON product.product_id = order_product.product_id "
         . " WHERE order_product.order_id = " . (int)$order_id;
    
    echo "<hr/>DEBUG SQL: " . $sql . "<hr/>\n";
    
    $order_product_query = $this->db->query( $sql )
    It's also not a good idea to use SELECT * any time, but especially not when you do a JOIN. The field product_id (at a minimum) will appear twice in the SELECT list when you do so, which can badly confuse your subsequent PHP code.

    I strongly recommend you change SELECT * to SELECT only the fields your PHP code will be using.

    And not to ask a dumb question, but... Why do you need the DB_PREFIX on the one table but not on the other table?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Aug 2002
    Posts
    86
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi thanks for replying.

    In this query I want to draw all the data from the table 'order_product' and just the data in the column 'image' from the table 'product'.

    What is the best way to write this then so I dont select too much unecessary data ?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    Code:
    $sql = "SELECT op.*, p.image FROM " . DB_PREFIX . "order_product AS op "
         . " LEFT JOIN product AS p ON p.product_id = op.product_id "
         . " WHERE op.order_id = " . (int)$order_id;
    Same question re DB_PREFIX applies.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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