one to many queries

Question

Alright, I've just finished normalizing a table. What I didn't really consider when I was doing it is that I now have a one to many relationship... being that my sql knowledge is very, very limited I'm not really sure how to approach this.

I tried grouping by the foreign key, but it only returns the last matching result and I want to return both of them as well as any results where there is only one fk.

Desired Output:

   Site         Found On             Score
   example.com  sourece1, source2    400
   example.net  sourece1             23
   example.org  sourece2             23

Where: siteScoring.url = found on siteScoring.votes = score media.file_url = site

Answer 1

Psuedo SQL till details arrive:

  SELECT t.file_url,
         CONCAT_WS(',', ss.url) 'Found On',
         SUM(ss.votes)
    FROM MEDIA t
    JOIN SITESCORING ss ON ss. = m. --just missing JOIN criteria
GROUP BY t.file_url

Answer 2

If you're using MySQL 5+ you can use GROUP_CONCAT(source) (in the select clause) to create the Found On column in your current GROUP BY query

EDIT: my mistake it's MySQL 4.1+: group_concat