Query SQL Select Column Matching From Another Table-CodePudding

edit : Sorry gurus, I have to rephrase my question since I forgot there are 3 tables in one query. 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   |
   | bread  | G002  | 2     | pcs   |   
   | soap   | G003  | 1     | pcs   |  
    -------- ------- ------- -------

and tbl_units as below :

    -------- ------------- -------   
   | code   |ucode        |qty    |
    -------- ------------- ------- 
   | KG001  | U001        | 10    |
    -------- ------------- -------

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 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      |
   | bread  | G002  | 2     | pcs   | 2      |
   | soap   | G003  | 1     | pcs   | 1      |
    -------- ------- ------- ------- --------

so if the code in goods doesn't have match in tbl_units then it will show just as qty in tbl_goods, but if they match then it will convert multiply from tbl_units

Thank you very much..really appreciated

regards

EDIT (might worked ?) : I try to modify from @danielpr query, and this is the result think it worked, please help to check it out

SELECT j.code,j.qty ,j.unit, IIF(j.unit=t.unit,j.qty*u.qty,j.fqty)  FROM tbl_goods j
LEFT JOIN tbl_units u on u.code ='K' || j.code 
LEFT JOIN tbl_sat t ON t.ucode =u.ucode  [WHERE j.code='G001']
GROUP BY j.code,j.qty

[WHERE ..] optional if omitted will list all items, but if I just want to check the cigar..just put WHERE CLAUSE

CodePudding user response：

SELECT
    tbl_goods.goods
,   tbl_goods.code
,   tbl_goods.qty
,   tbl_goods.unit
,   IF(tbl_goods.unit=tbl_units.conversion,tbl_goods.qty*tbl_units.qty,tbl_goods.qty) total 
FROM tbl_goods
LEFT JOIN tbl_units ON tbl_goods.code=tbl_units.code

on total column, we can match whether the unit in tbl_goods is same with tbl_units, which is pack.

If it is the same, then we multiply the pack qty in tbl_units with the pack in tbl_goods, else, just return the qty of tbl_goods.

CodePudding user response：

If I understand correct, you are looking for a combination of LEFT JOIN and CASE WHEN or COALESCE.

Here the CASE WHEN option:

SELECT g.goods, g.code, g.qty, g.unit, 
CASE WHEN u.conversion IS NULL 
  THEN g.qty
  ELSE g.qty * u.qty
  END AS total
FROM 
tbl_goods g
LEFT JOIN tbl_units u
ON g.code = u.code
AND g.unit = u.conversion;

As said, COALESCE could also do and is a bit shorter:

SELECT g.goods, g.code, g.qty, g.unit, 
g.qty * COALESCE(u.qty,1) AS total
FROM 
tbl_goods g
LEFT JOIN tbl_units u
ON g.code = u.code
AND g.unit = u.conversion;

But I think this option has a worse readability compared to CASE WHEN.

Therefore, I would prefer CASE WHEN here.

Try out: db<>fiddle