mySQL using value of a table as a field to get query

Question

I have two table.

Table Name : Field Name

table A : ID, product_ID

table B : product_ID, product_type,product_num

the data are like this

Table A

+++++++++++
id + product_id
+++++++++++
1  + apple
2 + orange
+++++++++++

Table B

+++++++++++++++++++++++++++++++
product_id + product_type + product_num
+++++++++++++++++++++++++++++++
apple + red + 10
apple + blue + 20
orange + red + 5
orange + blue + 15
+++++++++++++

Can I get the result like

++++++++++++++++++++++++++++++++++++++++++
product_id  + red_num + blue_num
++++++++++++++++++++++++++++++++++++++++++
apple       +   10    +    20
++++++++++++++++++++++++++++++++++++++++++
orange     +    5     +  15

score 0 · Accepted Answer · answered May 07 '16 at 02:54

Don't be confused, first of all, Table A should have: id+product-name. The product-id of table B have to refer to id of table A. To make so, both fields must keep the same format. For instance, integer. So your table B would look like follows.

product_id + product_type + product_num

1 + red + 10

1 + blue + 20

2 + red + 5

2 + blue + 15

To get what you want, you can try:

select unique product-name, B.product-num as red-num, C.product-num as blue-num from B, B as C, A where A.id=B.product-id and A.id=C.product-id and B.product-type="red" and C.product-type="blue";

I haven't tested it, since I haven't a mysql server running at the momment. Hope it works. Remove the unique if it doesn't work, you may get something similar to what you need.

Thanks. but anyway, I use the below solution after read your solution. `SELECT DINTINCT product_name , B.product_num as red_num , C.product_num as blue_num FROM A LEFT JOIN B ON A.product_id = B.product_id AND B.product_type="red" LEFT JOIN B AS C ON C.product_id = B.product_id AND C.product_type="blue" ` — Kenneth Chan, May 07 '16 at 03:45
If you understand what those LEFT JOIN means, that's OK :) Enjoy! — mhyst, May 07 '16 at 03:47

score 0 · Answer 2 · edited May 23 '17 at 12:19

Ok, this is a typical table pivot issue.

You can do like this;)

SQL Fiddle

Schema:

CREATE TABLE product
    (`product_id` varchar(6), `product_type` varchar(4), `product_num` int)
;

INSERT INTO product
    (`product_id`, `product_type`, `product_num`)
VALUES
    ('apple', 'red', 10),
    ('apple', 'blue', 20),
    ('orange', 'red', 5),
    ('orange', 'blue', 15)
;

Query:

SELECT 
    product_id,
    SUM(IF(product_type = 'red', product_num, 0)) AS red_num,
    SUM(IF(product_type = 'blue', product_num, 0)) AS blue_num
FROM product
GROUP BY product_id

Results:

| product_id | red_num | blue_num |
|------------|---------|----------|
|      apple |      10 |       20 |
|     orange |       5 |       15 |

You can take a look of MySQL pivot table.

Thanks for your contribution.:) – Kenneth Chan May 07 '16 at 03:46 — Kenneth Chan, May 07 '16 at 03:46

mySQL using value of a table as a field to get query

2 Answers2

product_id + product_type + product_num