Joining multiple tables lead to extend the single row with duplicated attributes

Question

I've 5 tables that I need to join together:

products:
  - product_id

orders
  - order_id

order_items
  - item_id
  - order_id
  - product_id

directsells
  - directsell_id

directsell_items
  - item_id
  - directsell_id
  - product_id

I'm using Zend-Framework, here's my code:

    $ProductsTable = new Products();
    $select = $ProductsTable->select();
    $select->from(array('P' => 'products'), array('product_id', 'product_name'));
    $select->setIntegrityCheck(false);

    $select->joinLeft(array('DI' => 'directsells_items'), 'DI.product_id = P.product_id', array('item_price', 'item_discount', 'qty'));
    $select->joinLeft(array('D' => 'directsells'), 'DI.directsell_id = D.directsell_id', array('directsell_number AS invoice_number', 'directsell_date AS invoice_date', 'invoice_status'));
    $select->where('DATE(D.directsell_date) BETWEEN "'. date('Y-m-d', strtotime($from_date)) .'" AND "'. date('Y-m-d', strtotime($to_date)) .'"');
    $select->where('D.invoice_status <> "Canceled"');

    $select->joinLeft(array('OI' => 'order_items'), 'OI.product_id = P.product_id', array('item_price', 'item_discount', 'qty'));
    $select->joinLeft(array('O' => 'orders'), 'OI.order_id = O.order_id', array('order_number AS invoice_number', 'order_date AS invoice_date'
                    ,'IF(`order_status` = "10", "Returned", "Active") AS invoice_status'
                ));
    $select->where('DATE(O.order_date) BETWEEN "'. date('Y-m-d', strtotime($from_date)) .'" AND "'. date('Y-m-d', strtotime($to_date)) .'"');
    $select->where('O.order_status <> "0"');

    $select->where('P.product_id = '. $product_id);

Right now I've got 2 entries in directsell_items:

And I have 1 single entry in order_items:

What I'm getting as a result of my query is the two entries from directsell_items merged with the one in order_item, like so:

I hope that everything is clear. I know it maybe a problem with my joining, but I couldn't figure it out.

Answer 1

It looks like your direct sells items are one kind sale of items and your order items are a second sale of items. The reason that you're only getting two records is when you're joining you're effectively extending what a row is. Not looking for additional rows.

As @Barmar indicated you need a UNION to handle this situation. A union effectively takes the record sets from two separate queries and returns them as one record set.

So, you're going to need something along the lines of (code not tested):

$ProductsTable = new Products();
$selectDirect = $ProductsTable->select();
$selectDirect->from(array('P' => 'products'), array('product_id', 'product_name'));

$selectDirect->joinLeft(array('DI' => 'directsells_items'), 'DI.product_id = P.product_id', array('item_price', 'item_discount', 'qty'));
$selectDirect->joinLeft(array('D' => 'directsells'), 'DI.directsell_id = D.directsell_id', array('directsell_number AS invoice_number', 'directsell_date AS invoice_date', 'invoice_status'));
$selectDirect->where('DATE(D.directsell_date) BETWEEN "'. date('Y-m-d', strtotime($from_date)) .'" AND "'. date('Y-m-d', strtotime($to_date)) .'"');
$selectDirect->where('D.invoice_status <> "Canceled"');
$selectDirect->where('P.product_id = '. $product_id);

$selectOrders = $ProductsTable->select();
$selectOrders->from(array('P' => 'products'), array('product_id', 'product_name'));
$selectOrders->joinLeft(array('OI' => 'order_items'), 'OI.product_id = P.product_id', array('item_price', 'item_discount', 'qty'));
$selectOrders->joinLeft(array('O' => 'orders'), 'OI.order_id = O.order_id', array('order_number AS invoice_number', 'order_date AS invoice_date','IF(`order_status` = "10", "Returned", "Active") AS invoice_status'));
$selectOrders->where('DATE(O.order_date) BETWEEN "'. date('Y-m-d', strtotime($from_date)) .'" AND "'. date('Y-m-d', strtotime($to_date)) .'"');
$selectOrders->where('O.order_status <> "0"');
$selectOrders->where('P.product_id = '. $product_id);

$select = $db->select()->union(array($selectDirect, $selectOrders));