Query SQL Select Column Matching From Another Table

Question

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

sorry, i thought the query for mysql and sqlite almost 95% identical ?, so I thought some MySql insight might help here — Imam, Nov 28 '22 at 12:13
@Imam My answer shows a correct query, what is now still unlcear to you? I linked a fiddle, so you can verify that. Note this answer is correct for your original question. Please undo your changes and create a new question if required. No one can replicate questions and answers when the question is edited after answers have been written. Accept a correct answer and open a new question in that case. — Jonas Metzler, Nov 28 '22 at 12:40
@JonasMetzler, yeah, you right. correct.. just notice for the link, and "play around" amazing.... thanks — Imam, Nov 28 '22 at 13:15
@Imam I edited my answer by adding a further query for your new situation. Please understand I will not delete the previous part, so people can replicate the "history" of your question. Please check if this solves your question. Next time, I think creating a new question should be prefered. — Jonas Metzler, Nov 28 '22 at 13:17
@JonasMetzler actually, nice word won't fit here, da#n, you are good... solve my "little" headache problem. one last question, if I want just to sum up the goods grouping , i tried use SUM ..not working.. should I raise new question ? — Imam, Nov 28 '22 at 13:36
@JonasMetzler never mind, i just add SUM before CASE WHEN, and things working. You just saved my life...LOL.. amazing. thanks anyway — Imam, Nov 28 '22 at 13:40
@danielpr yours worked as well..kudos, for two tables yours works well, though I add third table so need revised the question and just found out that break the rule — Imam, Nov 28 '22 at 15:05

Jonas Metzler · Accepted Answer · 2022-11-28T13:16:00.800

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

EDIT because the authour changed the question:

According to the new description, a further table is involved and the table structure is other than described before. So, the COALESCE option is not possible at all in this case. We require the CASE WHEN way here:

SELECT g.goods, g.code, g.qty, g.unit, 
CASE WHEN u.qty IS NULL OR u.ucode IS NULL OR 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 = CONCAT('K', g.code)
LEFT JOIN tbl_sat t ON u.ucode = t.ucode AND g.unit = t.unit;

New sample fiddle for this new situation: db<>fiddle

score 1 · Answer 2 · answered Nov 28 '22 at 10:07

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.

Query SQL Select Column Matching From Another Table

2 Answers2

Linked