CODEWITHSUNDEEP
mysql
Stefan
Stefan

Reputation: 259

Display size column as row for each color product in related table?

I'm looking for a query to read and combine three tables. Below, a simplified design of the database tables:

Table: Sizes             Table: Colors
+------+------+         +------+-------+
| id   | size |         | id   | color | 
+------+------+         +------+-------+
|    1 |   32 |         |    1 | red   |
|    2 |   34 |         |    2 | blue  |
|    3 |   36 |         |    3 | green |
|    4 |   38 |         |    4 | white |
+------+------+         +------+-------+

Table: Orders 
+------+------+-------+--------+
| id   | size | color | amount |
+------+------+-------+--------+
|    1 |    1 |     1 |      1 |
|    2 |    1 |     2 |      3 |
|    3 |    2 |     2 |      4 |
|    4 |    3 |     2 |      2 |
+------+------+-------+--------+

So, the query should return always all the sizes and return just the colors if there is actually a product in that color. So, with the product table above described, i would a query which is return the following data:

+-------+------+------+------+------+
|       | 32   | 34   | 36   | 38   |
+-------+------+------+------+------+
| red   |    1 |    0 |    0 |    0 |
| blue  |    3 |    4 |    2 |    0 |
+-------+------+------+------+------+

Of course, the query returns just the combination of the size, color and amount and a PHP script will generate the table of it. Could somebody help with it? Thanks in advance!

Upvotes: 0

Views: 1203

Answers (1)

Grijesh Chauhan
Grijesh Chauhan

Reputation: 58271

There is no PIVOT command (MySQL pivot tables (transform rows to columns)) in MySQL so your query will be static for size. That is why it is better to postponed this in application.

Thought if you have just finite and small domain for size column then you can use following Query I posted below:

mysql> SELECT 
    ->      c.color as color,
    ->      SUM(IF(s.size = 32, o.amount, 0)) as '32',
    ->      SUM(IF(s.size = 34, o.amount, 0)) as '34',
    ->      SUM(IF(s.size = 36, o.amount, 0)) as '36',
    ->      SUM(IF(s.size = 38, o.amount, 0)) as '38'
    ->  FROM `colors` c
    ->  INNER JOIN `order` o
    ->  INNER JOIN `sizes` s
    ->      WHERE c.`id` = o.`color` and s.`id` = o.`size`
    -> GROUP BY color 
    -> ;
+-------+------+------+------+------+
| color | 32   | 34   | 36   | 38   |
+-------+------+------+------+------+
| blue  |    3 |    4 |    2 |    0 |
| red   |    1 |    0 |    0 |    0 |
+-------+------+------+------+------+
2 rows in set (0.04 sec)

As you can see in IF conditions I use value of size that is what I means question is static. I am assuming all possible size can be either 32, 34, 36, 38.

Working demo @SQL Fiddle

Edit: As I am saying from starting If size values are unkown or domain is large then better is you postponed pivot work within your server script (e.g. PHP) still you can use following query to process in script: 

SELECT 
     c.color as color,
     s.size,
     o.amount  --Edit: added 
 FROM `colors` c
 INNER JOIN `order` o
 INNER JOIN `sizes` s
     WHERE c.`id` = o.`color` and s.`id` = o.`size`

See how does it works @SQL fiddle.

Upvotes: 1

Related Questions