MySQL Join different rows into different columns

Question

I'm having some issues trying to figure this out,

I can easily make it with a loop thus making a second query inside the first, but that doesn't seems to be efficient.

The objective is to obtain a array of rows that contain the song name and the various file types file names.

Here is simplified versions of the two tables:

Songs:

song_id*
song_title

Song_files:

file_id
file_song_id*
file_format
file_name

Wanted result:

[
    {
        "song_id": "1",
        "song_title": "Musica Teste 1",
        "song_mp3_filename": "song1.mp3",
        "song_wav_filename": "song1.wav",
        "song_flac_filename": "song1.flac"
    },
    {
        "song_id": "2",
        "song_title": "Musica Teste 2",
        "song_mp3_filename": "song2.mp3",
        "song_wav_filename": "song2.wav",
        "song_flac_filename": "song2.flac"
    },
    {
        "song_id": "3",
        "song_title": "Musica Teste 3",
        "song_mp3_filename": "song3.mp3",
        "song_wav_filename": "song3.wav",
        "song_flac_filename": "song3.flac"
    }
]

Is there a more efficient way to do this without looping via php?

Thanks!

What's your current query? SELECT song_id, song_title, song_extra_title, song_duration, song_mp3_filename, song_wav_filename, song_flac_filename FROM songs s JOIN song_files f ON s.song_id = f.file_song_id; will join the two tables and return those fields. — WOUNDEDStevenJones
– WOUNDEDStevenJones, Commented May 25, 2017 at 22:08
The columns song_extra_title, song_duration, song_mp3_filename don't exist, the join you suggested gives-me 1 song title per filetype, what i'm looking for is have a row per song, and on that row have the diferente file names — Fábio Garcia
– Fábio Garcia, Commented May 25, 2017 at 22:34
I get what you're looking for with the different filenames (WHERE file_format = 'mp3', etc. I'm assuming), but where do song_extra_title and song_duration come from? — WOUNDEDStevenJones
– WOUNDEDStevenJones, Commented May 25, 2017 at 22:44
Sorry, those are real columns that I have on the real table, but not mentioned them on the simplified version that I posted on my question. will fix that — Fábio Garcia
– Fábio Garcia, Commented May 25, 2017 at 22:52

PaulD · Accepted Answer · 2017-05-25 22:09:59Z

1

You can read about joins here or google for them:

http://www.sql-join.com/

You can do a join with the query builder as described here:

https://www.codeigniter.com/user_guide/database/query_builder.html

Here is an example:

$this->db->from('songs')
    ->join('song_files', 'songs.song_id = files.file_song_id')
    ->get()
    ->result_array();

answered May 25, 2017 at 22:09

PaulD

1,1711 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fábio Garcia Over a year ago

The suggested answer joins both tables and gives-me multiple times the same song, each row with a different format filename, what I'm looking for is just one row per song, that row containing the available formats

WOUNDEDStevenJones Over a year ago

depending on how much data is actually being returned, it's definitely an option to do the join and then loop through the data once to format it exactly how you want

Fábio Garcia Over a year ago

That option is on the table, just wen implementing it i thought "there must be a better way" :P If i can get any other way I will do it with loops

ghenghy · Accepted Answer · 2017-05-26 02:05:03Z

0

select 
  s.id as song_id,
  s.title as song_title,
  group_concat(concat_ws(':', f.format, f.name) SEPARATOR ',')  as files
from
  song s
  inner join file f
    on s.id = f.song_id
  group by s.id;

Gives:

+---------+----------------+---------------------------------------------+
| song_id | song_title     | files                                       |
+---------+----------------+---------------------------------------------+
|       1 | Musica Teste 1 | mp3:song1.mp3,wav:song1.wav,flac:song1.flac |
|       2 | Musica Teste 2 | wav:song2.wav,flac:song2.flac,mp3:song2.mp3 |
|       3 | Musica Teste 3 | flac:song3.flac,mp3:song3.mp3,wav:song3.wav |
+---------+----------------+---------------------------------------------+

answered May 26, 2017 at 2:05

ghenghy

3611 silver badge5 bronze badges

1 Comment

Fábio Garcia Over a year ago

Thanks! that solves the majority of the problem but the ideal would be having each file format filename on their row.

Fábio Garcia · Accepted Answer · 2017-05-26 11:26:37Z

Thanks ghenghy! Based o your query, i continued to research and managed to hack a query that suits my needs, it can be improved but it does the job:

select 
  s.song_id as song_id,
  s.song_title as song_title,
  MAX(IF(f.sfile_filetype = 'sample', f.sfile_file, NULL)) AS sample,
  MAX(IF(f.sfile_filetype = 'mp3', f.sfile_file, NULL)) AS mp3,
  MAX(IF(f.sfile_filetype = 'wav', f.sfile_file, NULL)) AS wav,
  MAX(IF(f.sfile_filetype = 'flac', f.sfile_file, NULL)) AS flac
from
  songs s
  inner join song_files f
    on s.song_id = f.sfile_song_id
  group by s.song_id

It gives each filename on their respective column

songs:

https://i.sstatic.net/Ps1rr.png

song_files:

https://i.sstatic.net/UDUyk.png

Result:

https://i.sstatic.net/ily44.png

-- update --

The same but using codeigniter's query builder:

$this->db->select('song_id, song_title, song_extra_title, song_duration');
$this->db->select("MAX(IF(song_files.sfile_filetype = 'sample', song_files.sfile_file, NULL)) AS sample", FALSE);
$this->db->select("MAX(IF(song_files.sfile_filetype = 'mp3', song_files.sfile_file, NULL)) AS mp3", FALSE);
$this->db->select("MAX(IF(song_files.sfile_filetype = 'wav', song_files.sfile_file, NULL)) AS wav", FALSE);
$this->db->select("MAX(IF(song_files.sfile_filetype = 'flac', song_files.sfile_file, NULL)) AS flac", FALSE);
$this->db->join('song_files','song_files.sfile_song_id = songs.song_id');
$this->db->group_by("song_id");
echo json_encode($this->db->get('songs')->result());

Collectives™ on Stack Overflow

MySQL Join different rows into different columns

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related