Mysql working with comma separated list - Junction

问题:

I have a Junction table with ProductID and Accessory column:

TABLE1

ProductID    Accessory
1            2
1            3  
2            1
2            4
2            5
3
4            1
5            2

It means that for the ProductID 2, it has the Accessory ProductIDs 1,4 and 5 ...

and i have THE TABLE 2 below which look like this THE GRP and ProductID is already provided, we need to fetch the accesories.

TABLE2

GRP     ProductID     accessories 
a       2             
b       3             
c       1             
d       4             
e       5 

so actually if using UPDATE it would be like this

TABLE2

UPDATE table2 t2
set t2.accessories = (SELECT GROUP_CONCAT(DISTINCT t1.Accessory) FROM table1 t1
                      WHERE t1.ProductID = t2.ProductID)

GRP     ProductID     accessories
a       2             1,4,5
b       3              
c       1             2,3
d       4             1
e       5             2

but i want to change the productIDs in the t2.accessories with the GRP character instead according to the t2.ProductID so that the FINAL table looks like this .

TABLE2

GRP     ProductID     accessories
a       2             c,d,e
b       3              
c       1             a,b
d       4             c
e       5             a

Here is the tables http://sqlfiddle.com/#!9/83ec9

回答1:

This should work for you:

UPDATE table2 T
SET T.accessories = IFNULL((SELECT foo FROM
                        (SELECT t1.ProductID,GROUP_CONCAT(t2.GRP) as foo
                            FROM table1 t1 
                            LEFT JOIN table2 t2 ON t1.Accessory = t2.ProductID 
                            GROUP BY t1.ProductID
                        ) AS bar WHERE ProductID = T.ProductID
                     ),'')

Here is the updated SQL Fiddle

回答2:

You need to have a third table that links table 1 with table 2. The accessories column in table two breaks the first normal form (Database Normal Form). There should never be a cell with more than one piece of data. This increases redundancy and makes it impossible to retrieve any individual accessory without pulling it out and parsing it.

So, table 1 would have the productID and the GRP. Then there needs to be an accessory table with just the accessories. Table 2 would have the primary keys from both (productID, accessory). In order to get them out you would just natural join all three. This reduces the redundancy and allows you to have a GRP and product that does not have any accessories.

So you would have:

Table 1

GRP

productID

Table 2

productID

Accessory

Table 3

Accessory