1

There are two tables with similar contents.I need to join them on the key 'id' to compare the value.They look like below:

table_A

id   item_name   item_value
1    cost         1.00
1    price        3.15
1    tax          0.15
2    cost         9.00
2    price        17.66
2    tax          1.00

table_B

id   cost   price   tax
1    1.20   3.80    0.20
2    8.95   15.65   0.98

what I want :

id   item_name   item_value_a   item_value_b
1    cost         1.00          1.20
1    price        3.15          3.80
1    tax          0.15          0.20
2    cost         9.00          8.95
2    price        17.66         15.65
2    tax          1.00          0.98

How to achieve this result by sql syntax?

I have tried using union all clause to convert table_B like below:

select id,'cost' as item_name,cost as item_value
from table_B 
union all
select id,'price' as item_name,price as item_value
from table_B
union all
select id,'tax' as item_name,tax as item_value
from table_B

I can get a table which is the same as table_A on table structure.Then I join them on id and item_name and select both item_value. I'm confused that if this method is the most efficient way to solve the issue.When there are plenty of columns,should I use so many union all clauses?

I use mysql version 8.0

thx for advice anyway.

pansh
  • 13
  • 3
  • Are we to assume that for every row in a there will be a column in b? – P.Salmon Nov 29 '22 at 09:37
  • `table_B` looks like a matrix and that's no good fit for a relational database. Please consider normalizing `table_B`; give it a structure that is similar to `table_A`. This will make the query simpler (straightforward join) and probably also more scalable. – Ruud Helderman Nov 29 '22 at 09:42
  • @P.Salmon yes,u can assume like that – pansh Nov 29 '22 at 10:32
  • 2
    This arrangement, as you no doubt now know, is going to be both a querying and performance headache; tableb should really be treated to some normalisation. – Stu Nov 29 '22 at 10:33

1 Answers1

1

This is a nice use case for CASE operator:

SELECT a.id, a.item_name, a.price,
CASE a.item_name 
WHEN 'cost' THEN b.cost 
WHEN 'price' THEN b.price 
WHEN 'tax' THEN b.tax
END
FROM table_A a LEFT JOIN table_B b ON a.id=b.id;
Sergey Kudriavtsev
  • 10,328
  • 4
  • 43
  • 68
  • In fact there are about 50 different items.Should I use `case` operator 50 times..? – pansh Nov 29 '22 at 09:45
  • 2
    @pansh You mean `table_B` has 50 such columns? Then the answer is yes. That's the price you pay for not normalizing `table_B`. – Ruud Helderman Nov 29 '22 at 10:03
  • @pansh In short, yes. What you're looking for is called a pivot table. Some RDBMS (e.g. Oracle) support separate syntax for generating these, but MySQL doesn't, so you will have to stick to a multitude of `CASE` operators. You can automate it a bit by using dynamic SQL, but this brings its own challenges: https://www.databasestar.com/mysql-pivot/. Some more links on topic: https://stackoverflow.com/a/7675121/625594 – Sergey Kudriavtsev Nov 29 '22 at 10:57