SQL Join Query with three tables

Question

I have three tables.

1st table item_stock(item_id,item_name,item_current_qty)

2nd table item_purchase(item_id,item_purchase_qty,item_purchase_date)

3rd table item_transaction(item_id,item_txn_to,item_txn_qty,txn_date)

Item stock table has details of what on hand qty we have of all items, item_purchase table has information of which item was purchase with what quantity and item_transaction table has details of transaction happened for all items.

What I have tried is :

  select item_stock.item_name,item_stock.item_current_qty as on_hand,sum(item_purchase.item_purchase_qty) as purchased ,sum(item_transaction.item_txn_qty) as Txnd
  from item_stock
  full outer join item_purchase on item_purchase.item_id=item_stock.item_id
  full outer join item_transaction.item_id=item_stock.item_id
  where item_purchase.item_purchase_date between '2022-11-01' and '2022-11-30' 
    and item_transaction.txn_date between '2022-11-01' and '2022-11-30' 
  group by item_purchase.item_id,item_transaction.item_id
  order by item_stock.item_name asc

Output:

----------------------------------------------
--item_name----on_hand---purchased----Txnd----
-- TN2280  --- 25     ---   6     ---  25 ----
-- TN2590  --- 14     ---   8     ---  26 ----
----------------------------------------------

Output is showing only items which are purchased and transacted.

What I need is:

I need a table output which will display all items of item_stock with pattern like; "item_name","on_hand","purchased","Txnd". I want to put date filter in 2nd and 3rd table. For example in November 2022 what qty was purchased for each item and how many items were transacted in the same month.

Answer 1

So the problem is you are using full outer joins and then including the joined tables in the where clause -- since where will be false for null items you are not getting results from a full outer join you are getting results for an inner join -- the null items are discarded.

In order to fix this you need to use left joins (probably what you did first) and then put the criteria in the join. With a left join you won't need the group by and you will just get the results you want. Like this:

  SELECT item_stock.item_name,
         item_stock.item_current_qty as on_hand,
         sum(item_purchase.item_purchase_qty) as purchased,
         sum(item_transaction.item_txn_qty) as Txnd
  FROM item_stock
  LEFT JOIN item_purchase ON item_purchase.item_id=item_stock.item_id
    AND item_purchase.item_purchase_date between '2022-11-01' and '2022-11-30'
  LEFT JOIN item_transaction ON item_transaction.item_id=item_stock.item_id
    AND item_transaction.txn_date between '2022-11-01' and '2022-11-30' 
  GROUP BY item_purchase.item_id, item_transaction.item_id
  ORDER BY item_stock.item_name ASC