Mysql - union query

Question

I have a product table as follows where a product can have 3 different packsize and prices:

  id    | packsize1 | price1 | packsize2 | price2   | packsize3 | price3
-------------------------------------------------------------------------------
  1     |    1g     |  10.00 |     5g    |   25.00  |    10g    |  50.00
  2     |    1g     |  12.00 |     5g    |   27.00  |    10g    |  55.00
  3     |    5g     |  30.00 |     10g   |   40.00  |    50g    |  60.00
  4     |    2g     |  20.00 |     5g    |   35.00  |    50g    |  60.00
  5     |    1g     |  10.00 |     5g    |   35.00  |    10g    |  60.00

how can I return a list of products with packsizes and prices for only 1g, 5g and 10g quantites as follows:

  id    | packsize1 | price1 | packsize2 | price2   | packsize3 | price3
-------------------------------------------------------------------------------
  1     |    1g     |  10.00 |     5g    |   25.00  |    10g    |  50.00
  2     |    1g     |  12.00 |     5g    |   27.00  |    10g    |  55.00
  3     |    1g     |  null  |     5g    |   30.00  |    10g    |  40.00
  4     |    1g     |  null  |     5g    |   35.00  |    10g    |  null
  5     |    1g     |  10.00 |     5g    |   35.00  |    10g    |  60.00

Are you looking for packsize1 to always be 1g, packsize2 to always be 5g, and packsize3 to always be 10g like your second example? — GenericDisplayName, Jul 19 '19 at 16:33
This problem is made more complicated than necessary by poor design. Consider revising. — Strawberry, Jul 19 '19 at 16:33
This database design is unfortunate. You should rather have a table with columns _product_id, packsize, price_ only. That would make this query and others much easier to write. — Thorsten Kettner, Jul 19 '19 at 16:35
@Strawberry i cannot revise it as don't own the database, i have to work with what i've got. — adam78, Jul 19 '19 at 16:47

score 0 · Answer 1 · answered Jul 19 '19 at 16:38

A normalised design might look like this. From here, the problem is trivial...

+----+----------+----------+-------+
| id | packsize | weight_g | price |
+----+----------+----------+-------+
|  1 |       1  |        1 | 10.00 |
|  2 |       1  |        1 | 12.00 |
|  3 |       1  |        5 | 30.00 |
|  4 |       1  |        2 | 20.00 |
|  5 |       1  |        1 | 10.00 |
|  1 |       2  |        5 | 25.00 |
|  2 |       2  |        5 | 27.00 |
|  3 |       2  |       10 | 40.00 |
|  4 |       2  |        5 | 35.00 |
|  5 |       2  |        5 | 35.00 |
|  1 |       3  |       10 | 50.00 |
|  2 |       3  |       10 | 55.00 |
|  3 |       3  |       50 | 60.00 |
|  4 |       3  |       50 | 60.00 |
|  5 |       3  |       10 | 60.00 |
+----+----------+----------+-------+

score 0 · Accepted Answer · answered Jul 19 '19 at 16:41

You can unpivot and re-pivot the data. In MySQL, this looks like:

select id,
       '1g' as packsize_1g,
       max(case when packsize = '1g' then price end) as price_1g,
       '5g' as packsize_5g,
       max(case when packsize = '5g' then price end) as price_5g,
       '10g' as packsize_10g,
       max(case when packsize = '10g' then price end) as price_10g,
from ((select id, packsize1 as packsize, price1 as price
       from t
      ) union all
      (select id, packsize2, price2
       from t
      ) union all
      (select id, packsize3, price3
       from t
      )
     ) t
where packsize in ('1g', '5g', '10g')
group by id;

score 0 · Answer 3 · answered Jul 19 '19 at 16:43

Try this-

SELECT ID,
CASE WHEN packsize1 = '1g' THEN packsize1 WHEN packsize2 = '1g' THEN packsize2 WHEN packsize3 = '1g' THEN packsize3 END packsize1,
CASE WHEN packsize1 = '1g' THEN price1 WHEN packsize2 = '1g' THEN price2 WHEN packsize3 = '1g' THEN price3 END price1,

CASE WHEN packsize1 = '5g' THEN packsize1 WHEN packsize2 = '5g' THEN packsize2 WHEN packsize3 = '5g' THEN packsize3 END packsize2,
CASE WHEN packsize1 = '5g' THEN price1 WHEN packsize2 = '5g' THEN price2 WHEN packsize3 = '5g' THEN price3 END price2,

CASE WHEN packsize1 = '10g' THEN packsize1 WHEN packsize2 = '10g' THEN packsize2 WHEN packsize3 = '10g' THEN packsize3 END packsize3,
CASE WHEN packsize1 = '10g' THEN price1 WHEN packsize2 = '10g' THEN price2 WHEN packsize3 = '10g' THEN price3 END price3

FROM product

Mysql - union query

3 Answers3