mysql join but turns two rows in the joined table into two columns-CodePudding

I have a join between the two tables below (info and files).
What I want is the join to look like the third table layout below.

For each record in the info table, I know there will be two rows with the same info_id in the files table.

I am not sure whether I need mysql's version of a pivot table or something else. I am still searching as to how to accomplish this but am reaching out as well. Any assistance pointing me in the right direction would be greatly appreciated.

info table
id  name
1   Peter
2   Jane

files table
id  info_id filename
1   1       peter_file_1
2   1       peter_file_2
3   2       jane_file_1
4   2       jane_file_2

what I want is:

id  name    file1           file2
1   Peter   peter_file_1    peter_file_2
2   Jane    jane_file_1     jane_file_2

Thanks! Peter T

CodePudding user response：

Because you know you'll always have 2 files you can simplify a traditional pivot by using a correlated subquery for each file:

select i.Id, i.Name,
    (select filename from files f where f.info_id=i.id order by f.id limit 1) file1,
    (select filename from files f where f.info_id=i.id order by f.id desc limit 1) file2
from info i

See Example fiddle

CodePudding user response：

If your version of MySql is 8.0 you can use ROW_NUMBER() window function to rank the filename of each info_id and then use conditional aggregation:

SELECT i.id, i.name,
       MAX(CASE WHEN f.rn = 1 THEN f.filename END) file1,
       MAX(CASE WHEN f.rn = 2 THEN f.filename END) file2
FROM info i INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY info_id ORDER BY id) rn
  FROM files 
) f ON i.id = f.info_id
GROUP BY i.id, i.name;

For previous versions use GROUP_CONCAT() and SUBSTRING_INDEX():

SELECT i.id, i.name,
       SUBSTRING_INDEX(GROUP_CONCAT(f.filename ORDER BY f.id), ',', 1) file1,
       SUBSTRING_INDEX(GROUP_CONCAT(f.filename ORDER BY f.id), ',', -1) file2
FROM info i INNER JOIN files f
ON i.id = f.info_id
GROUP BY i.id, i.name;

Or, if there is a case that there is only 1 file for a specific info_id:

SELECT i.id, i.name,
       SUBSTRING_INDEX(GROUP_CONCAT(f.filename ORDER BY f.id), ',', 1) file1,
       CASE WHEN COUNT(*) > 1 THEN SUBSTRING_INDEX(GROUP_CONCAT(f.filename ORDER BY f.id), ',', -1) END file2
FROM info i INNER JOIN files f
ON i.id = f.info_id
GROUP BY i.id, i.name;

See the demo.