Combining mulitple tables into one

Transact-SQL
1

Hi I have 5 tables
Table 1 - item_code, allocation_qty
Table 2 - item_code, issue_qty
Table 3 - item_code, return_qty
Table 4 - item_code, po_pending_Qty
Table 5 - item_code, stock_qty

Now i wish to combine the data in Table 6 as follows (eg)

item_code, allocation_qty, issue_qty, return_qty, po_pending_qty, stock_qty
123 100 50 20 200 500
234 - - - 300 100
456 50 0 0 200 200
can anybody help pls

2

A simple set of joins should do the job. Hint, join on item_code. If the item_code is in all tables use an inner join else a left / right join.

With that said let us know if you get stuck.

3

Hi thanks for your reply. However, pls note that inner / left / outer join will not work, b''cos table 1 item codes may or may not match with table 2 / table 3 / table 4 or table 5. Also, table 1 may have 50 item codes - table 2 may have 75 item codes - table 3 may have 20 item codes / table 4 may have 200 item codes and table 5 may have 300 item codes and so on. Is it possible to join these tables using union all - but the problem is 1st column name is matching whereas other columns are different. - union all is working but it is clubbing all the values into same column. whereas i need to categorise as allocation / issues / returns / po pending / stock. this is where i am getting struck. Is there a way ?

4

I think the correct technical solution is FULL OUTER JOINs. However, I've seen them perform terribly slowly. If they do, we can switch you to the UNION ALL method:

SELECT
    COALESCE(t1.item_code, t2.item_code, t3.item_code, t4.item_code, t5.item_code) AS item_code,
    t1.allocation_qty,
    t2.issue_qty,
    t3.return_qty,
    t4.po_pending_qty,
    t5.stock_qty    
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t2.item_code = t1.item_code
FULL OUTER JOIN table3 t3 ON t3.item_code IN ( t1.item_code, t2.item_code )
FULL OUTER JOIN table4 t4 ON t4.item_code IN ( t1.item_code, t2.item_code, t3.item_code )
FULL OUTER JOIN table5 t5 ON t5.item_code IN ( t1.item_code, t2.item_code, t3.item_code, t4.item_code )
1 Like
5

Hey Scott. Thanks so much. Your solution worked and it is not slowing down!l. Your reply shows your understanding and expertise in resolving the issues. Thanks once again. Good to be associated with guys like you.