-2

I have three tables with tbl_goods ,tbl_units and tbl_sat which looks like this :

tbl_goods, consists of sold goods

   +--------+-------+-------+-------+  
   | goods  |code   |qty    |unit   |  
   +--------+-------+-------+-------+   
   | cigar  | G001  | 1     | pack  |
   | cigar  | G001  | 2     | pcs   |
   | cigar  | G001  | 2     | box   |
   | bread  | G002  | 2     | pcs   |
   | bread  | G002  | 2     | pack  |   
   | soap   | G003  | 1     | pcs   |  
   +--------+-------+-------+-------+

and tbl_units as below :

   +--------+-------------+-------+  
   | code   |ucode        |qty    |
   +--------+-------------+-------+
   | KG001  | U001        | 10    |
   | KG001  | U002        | 20    |
   | KG002  | U001        | 15    |
   +--------+-------------+-------+

I add letter 'K' in front of code in tbl_units to differ and make sure not collide with code in tbl_goods.

and tbl_sat as below :

   +--------+-------------+ 
   | ucode  | unit        |
   +--------+-------------+
   | U001   | pack        |
   +--------+-------------+
   | U002   | box         |
   +--------+-------------+
   | U003   | crate       | etc

so only cigar and bread will have conversion because table units have the code

what the result I need to show as below :

   +--------+-------+-------+-------+--------+  
   | goods  |code   |qty    |unit   | total  |
   +--------+-------+-------+-------+--------+   
   | cigar  | G001  | 1     | pack  | 10     |
   | cigar  | G001  | 2     | pcs   | 2      |
   | cigar  | G001  | 2     | box   | 40     |
   | bread  | G002  | 2     | pcs   | 2      |
   | bread  | G002  | 2     | pack  | 30     |
   | soap   | G003  | 1     | pcs   | 1      |
   +--------+-------+-------+-------+--------+

Actually this question already been asked and might similar but the changes in table structure and coding, so I think I start another new question. Hopefully this can solve my problem

Thank you very much

Regards

Imam
  • 37
  • 6
  • 1
    [This Might Help?] Surprisingly it has similar question with yours(https://stackoverflow.com/questions/74598654/query-sql-select-column-matching-from-another-table) – DoDoX Nov 29 '22 at 09:57
  • yes, true, but the result actually only allowed one code in tbl_units, i tried to change the question but not allowed by the rules. So I try new question with revised output. Last question not wrong but only allowed with one result – Imam Nov 29 '22 at 10:02

1 Answers1

0

I try several ways, and I have to use the "hard way". First I find the quantity which not the smallest form ('pcs') by using query as below :

SELECT g.goods, g.qty , g.unit , t.unit, u.ucode, u.qty,  
(CASE 
  WHEN  t.unit IS NULL
  THEN g.qty 
  ELSE g.qty * u.qty 
  END) AS total
FROM 
tbl_goods g
LEFT JOIN tbl_units u ON u.code= 'K' || g.code
LEFT JOIN  tbl_sat t ON u.ucode= t.ucode AND g.unit=t.unit
WHERE t.unit  IS NOT NULL

After I get the list for items goods without smallest form, I make another query to get list with smallest form

SELECT g.goods, g.qty, g.unit,g.code
FROM 
tbl_goods g  
WHERE g.unit='Pcs'

After that, I combined both of the query Hopefully the speed might good enough for large records

Regards

Imam
  • 37
  • 6