Trouble denormalizing -- help with JOIN

[phripley]

Am trying to denormalize a table.

 

Can't seem to get UPDATE w/ JOIN correctly

 

What I have now:

 

exhibits_table

exhibit_id

1

2

3

 

 

related_exhibits

related_from_id  | related_to_id

1                      | 2

1                      | 3

2                      | 1

 

 

 

What I am looking to do is move the data from the related_exhibits table into a single concatenated field in the exhibits_table:

 

 

exhibits_table

exhibit_id  |  related_id

1              |  2,3

2              |  1

 

 

I thought I would want something like this:

 

UPDATE related_exhibits

LEFT JOIN exhibits_table

ON exhibits_table.exhibit_id = related_exhibits.related_from_id

SET  exhibits_table.related_id = CONCAT_WS(",", exhibits_table. related_id, related_exhibits.related_to_id)

 

 

Any help would be greatly appreciated.

[gluck]

Why do you wanna do that?

Did you try the query?

[phripley]

Why:

 

We are moving the data into a CMS.  There's no easy want to get the CMS to work with the join table.  But it will handle the joins when they are called as described above.

 

 

 

[fenway]

Why:

 

We are moving the data into a CMS.  There's no easy want to get the CMS to work with the join table.  But it will handle the joins when they are called as described above.

 

 

 

I don't know what that means... you mean to say your CMS can only handle unnormalized tables?

[phripley]

Not exactly fenway, it doesn't handle many to many self relationships in a way that has an acceptable UI.

 

Actually to tell the truth the denormalization I am doing is an interim step before we do something else with the data, but I am not involved with the something else directly. I was just told to deliver it in a single delimited column.

[fenway]

Hmmm... then I suppose CONCAT_WS() will work.

[Barand]

try

UPDATE exhibits_table e
JOIN (SELECT related_from_id, GROUP_CONCAT(related_to_id SEPARATOR ',') as idlist FROM related_exhibits GROUP BY related_to_id) as x
    ON e.exhibit_id = x.related_from_id
SET e.related_id = x.idlist