user911712 user911712 - 6 months ago 17
SQL Question

sql query to display the balances of two table

Example

table1 considered as purchasing


barcode articleno Description size
0101010 500845-400 nike shoes 7
10125 500845-400 nikeshoes 8
0101010 500845-400 nike shoes 7
0101010 500845-400 nike shoes 7
1254 125456-700 nikeshoes 12


Table2 considered as trasfering stock

barcode articleno Description size
0101010 500845-400 nike shoes 7
10125 500845-400 nikeshoes 8


OUTPUT : balace stocks

barcode articleno Description size
0101010 500845-400 nike shoes 7
0101010 500845-400 nike shoes 7
1254 125456-700 nikeshoes 12

Answer

There is a way to do this. The idea is to remove n rows from the first table for a given barcode/size combination (I'm assuming that is the unique identifier for rows). The value of n is the number of rows in the second table.

The following query does this by removing the first n rows based on id. It filters out the rows where the number of preceding ids is less than count:

select p.*
from purchasing p left outer join
     (select barcode, size, count(*) as cnt
      from transfers t
      group by barcode, size
     ) bc
     on p.barcode = bc.barcode and p.size = bc.size
where coalesce(cnt, 0) < (select count(*)
                          from purchasing p2
                          where p2.barcode = p.barcode and
                                p2.size = p.size and
                                p2.id <= p.id
                         );