I have 2 tables - news and files. The files table has a news_id foreign key. There may be multiple rows of files with the same news_id. Each news_id has up to 3 files, some have none; for example one news article may have 2 images associated with it and another only one etc. I want to select the 'name' from the files table, which matches the news_id and insert the 'name' into the news table - into cols: file_1, file_2, file_3.

files

news

The select part of the query retrieves the files I need. I just cant seem to work out the insert as there are different amount of files for each news ID. I have tried this query:

INSERT INTO news(files_1,files_2,files_3)
SELECT t2.name
FROM files t2
left JOIN news t1 ON t2.file_id = t1.news_id
WHERE t2.cat_id LIKE 13

And:

INSERT INTO news(files_1, files_2, files_3)
SELECT COALESCE( t2.name, '')
FROM files t2
LEFT JOIN news t1 ON t2.file_id = t1.news_id
WHERE t2.cat_id LIKE 13

The error I get is: column count doesn't match value count at row 1.

I am trying to achieve something like:

news

CodePudding user response：

This answer: [1]: https://dba.stackexchange.com/questions/128668/insert-data-from-multiple-rows-into-multiple-columns

helped me write the following query, which achieved the desired result:

 SELECT t1.news_id
  , COALESCE(grp.file_1, '') as file_1
  , COALESCE(grp.file_2, '') as file_2
  , COALESCE(grp.file_3, '') as file_3
FROM news t1
INNER JOIN (
   SELECT d.news_id as news_id
   , MAX(CASE WHEN row = 1 THEN d.name END) AS file_1
   , MAX(CASE WHEN row = 2 THEN d.name END) AS file_2
   , MAX(CASE WHEN row = 3 THEN d.name END) AS file_3
    FROM
    (
      SELECT @row := CASE WHEN t2.news_id = @news_id 
          THEN @row   1 ELSE 1 END as row 
          , @news_id := t2.news_id as news_id
          , t2.name
          FROM (SELECT @row := 0, @news_id := 0) v
        , files as t2 
      ORDER BY t2.news_id
    ) d 
    GROUP BY d.news_id
) grp
    ON grp.news_id = t1.news_id;