mySQL JOIN syntax using bridging table

Question

Hi folks,

I am trying to export some data from two tables bridged by a third table that stores the one (file) to many (keywords) relationship.

Tables are like so:

files
id, url, title

keywords
id, word

bridge
file, word

What I want is to produce an export that has one row per file like this:

files.id, files.url, files.title, keyword1|keyword2|keyword3|...

Any advice greatly appreciated!

JG

Answer 1

You can use GROUP_CONCAT to combine the keywords in a GROUP BY query:

SELECT 
  files.id, files.url, files.title,
  GROUP_CONCAT(keywords.word ORDER BY keywords.word SEPARATOR '|') keywords
FROM
  files
  LEFT OUTER JOIN bridge ON bridge.file = files.id
  LEFT OUTER JOIN keywords ON keywords.id = bridge.word
GROUP BY
  files.id, files.url, files.title