Page 1 of 1

mysql join many records onto one

Posted: Fri May 23, 2008 4:38 am
by panic!
Hello all,

Here's my problem.

I have a table something like this:


PAGE

Code: Select all

id title                         content
1 home                        <b> home </b>
2 links                         <a href="http://www.google.com">good search engine </a>
3 about us                    We love you
4 news                         22 May 2008<br/>new website.
and another table something like this

EXTRACONTENT

Code: Select all

 
id  page_id                 content
1   2                           <a href="http://www.yahoo.com">another search engine</a>
2   2                           <a href="http://www.altavista.com">does this even exist?</a>
3   2                           <a href="http://www.ytmnd.com">junk</a>
4   3                           We also love ourselves
5   4                           21 May 2008 <br/> I love LAMP
 


What I would like to return is each record from the page table with all the relevant fields from the extracontent table in one field at the end of the query in one column.


If I were to use something like

Code: Select all

select * from page p left join extracontent e on p.id=e.page_id where p.id=2
it would return a row for each record from the extracontent table join on. i.e.:

Code: Select all

 
id          title                  content             id       page_id      content
2           links                <a href="...        1        2               <a href="http://www.yahoo.com">another search engine</a>
2           links                <a href="...        2        2               <a href="http://www.altavista.com">does this even exist?</a>
2           links                <a href="...        3        2               <a href="http://www.ytmnd.com">junk</a>
 
when what I'd like is the additional content rows of the extracontent table concatinated into one field at the end of one record.

like this

Code: Select all

 
id          title                  content             id       page_id      content
2           links                <a href="...        1        2               <a href="http://www.yahoo.com">another search engine</a> <a href="http://www.altavista.com">does this even exist?</a> <a href="http://www.ytmnd.com">junk</a>
 

my examples are simplifying the problem that I'm encountering and I want the content at the end of the table for fulltext searching if that makes any difference.


Anyway thank you in advance for reading my massive post.