mysql join many records onto one

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
panic!
Forum Regular
Posts: 516
Joined: Mon Jul 31, 2006 7:59 am
Location: Brighton, UK

mysql join many records onto one

Post 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.
Post Reply