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.
This was first published in September 2003