joining multiple tables with left join

Question

I have the following query:

$products_query = 
  tep_db_query("select op.orders_products_id, op.orders_id, op.products_id, ".
                      "op.products_model, op.products_name, op.products_quantity, ".
                      "p.products_id from " . TABLE_ORDERS_PRODUCTS . " op " .
                  " left join " . TABLE_PRODUCTS . " p " .
                  " on (op.products_id = p.products_id) where orders_id = '" . 
                  (int)$cuttinglist['orders_id'] . "'");

This joins two tables together with the same id. How would I join a 3rd table called:

"TABLE_ORDERS_PRODUCTS_ATTRIBUTES"

with the same id as:

"TABLE_ORDERS_PRODUCTS"

The ID used is:

"orders_products_id"

Fluffeh · Accepted Answer · 2012-09-05 11:43:19Z

2

To make it easier to read your code (and so you don't need to have so many concats and quotes everywhere you can do a query like this:

$sql='
select 
    op.orders_products_id, 
    op.orders_id, op.products_id,
    op.products_model, 
    op.products_name, 
    op.products_quantity,
    p.products_id 
from 
    '.TABLE_ORDERS_PRODUCTS.' op 
        left join '.TABLE_PRODUCTS.' p 
            on op.products_id = p.products_id
        left join '.TABLE_ORDERS_PRODUCTS_ATTRIBUTES.' Attrib
            on op.orders_products_id=Attrib.orders_products_id
where 
    orders_id = "' .(int)$cuttinglist['orders_id'] . '"
';
$products_query = tep_db_query($sql);

edited Sep 5, 2012 at 11:43

answered Sep 5, 2012 at 11:31

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sherlock Over a year ago

TABLE_ORDERS_PRODUCTS amd TABLE_PRODUCTS are PHP constants, so you do need those quotes (or replace it some other way).

Jens · Accepted Answer · 2012-09-05 11:31:09Z

1

$products_query = 
  tep_db_query("select op.orders_products_id, op.orders_id, op.products_id, ".
                  "op.products_model, op.products_name, op.products_quantity, ".
                  "p.products_id from " . TABLE_ORDERS_PRODUCTS . " op " .
              " left join " . TABLE_PRODUCTS . " p " .
              " on (op.products_id = p.products_id) "
              " left join " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " pa " .
              " on (op.products_id = pa.orders_products_id) where orders_id = '" . 
              (int)$cuttinglist['orders_id'] . "'");

answered Sep 5, 2012 at 11:31

Jens

2,0751 gold badge14 silver badges30 bronze badges

Collectives™ on Stack Overflow

joining multiple tables with left join

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related