Combining a LEFT OUTER and INNER join

I have three tables:

Item_mst

 item_id
 item_name
 qty_hnd
 qty_cmt

Trx_hd
 trx_id
 trx_dt
 cust_id
 gro_amt
 dis_amt

Trx_dtl
 trx_id
 trx_type
 item_id
 supp_id
 cust_id
 trx_qty

The transaction key (trx_id) is the common field for Trx_hd and Trx_dtl. The item key (item_id) is the common field for Item_mst and Trx_dtl. In the table Trx_dtl all the transactions are there. In the Item_mst table I have the stock_on_hand of the Items.

I'm trying to extract the stock and sales of the Items in a particular date range and for a specified supp_id. But if there are no transactions I couldn't get that item. Can you help me?

Let's start with the sales. You want the sales of a particular date range, for a specified supp_id. This is accomplished by an inner join between the transaction header and the transaction detail tables:

select sum(trx_qty) as sales
  from Trx_dtl D
inner 
  join Trx_hd H
    on D.trx_id = H.trx_id
 where H.trx_dt between '2003-08-13' 
                    and '2003-09-09' 
   and D.supp_id = 937

There's no GROUP BY yet, but that's because we're not finished.

Now let's add the other requirement: stock. This will come from the item table. However, if a particular item has no sales, you still want to see the stock. This is accomplished by a left outer join from the item table to the transaction tables.

select item_id
     , item_name
     , qty_hnd      as stock
     , sum(trx_qty) as sales
  from Item_mst I
left outer
  join Trx_dtl D
    on I.item_id = D.item_id
inner 
  join Trx_hd H  
    on D.trx_id = H.trx_id
 where H.trx_dt between '2003-08-13' 
                    and '2003-09-09' 
   and D.supp_id = 937
group
    by item_id
     , item_name
     , qty_hnd

My experience includes many databases: DB2, SQL Server, Oracle, Access, and MySQL. Somewhere along the line, one of them (can't remember which one, and this issue was probably fixed in a later version) would not allow you to use INNER after LEFT OUTER. No matter. You can safely change the INNER to LEFT OUTER as well, even though it is unlikely (impossible, if you have declared Relational Integrity via foreign keys) that there will ever be transaction detail rows without a matching transaction header. With LEFT OUTER joins from Item_mst to Trx_dtl to Trx_hd, you will still get the same result, because of the way the SUM() function works. Aggregate functions ignore nulls, and of course you get nulls in a LEFT OUTER join for unmatched rows, such as stock without any sales for the specified date range and supplier.

In Access, of course, you need to parenthesize your joins, but that's another rant for another day.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.