joining 3 tables, one of which has multiple entries

[mrsrowe]

MySql version 4.0.27 php version 4.4.4

 

cryptic huh.

 

I'm slow at this so please bear with me.  I don't even know what to call what I'm looking for, so if the answer is already on the forum, I apologise for repeating the question.

 

I have a table which contains a list of documents.  Each of these documents has key words, some documents have multiple keywords.  I have many documents and few keywords so I thought the best thing to do would be to create a table that held the keywords and join the two tables with a third table.

 

I want to print out the name of a document in a table with a table cell that contains the key words relevant to the document

 

Example

 

Doc Title 1 | keyword 1, Keyword 2 |

Doc Title 2 | keyword 1, Keyword 3 |

Doc Title 3 | keyword 2                |

 

I have a query which returns each document with the keywords associated with it, but it creates a new record for each entry so the results look like this:

 

Doc Title 1 | keyword 1 |

Doc Title 1 | keyword 2 |

Doc Title 2 | keyword 1 |

Doc Title 2 | keyword 3 |

 

Each record is unique because of the different keywords, but I only want the record to appear once and the associated keywords to appear seperated with a ','

 

This is my query:

 

SELECT * FROM tblDoc, tblKeyW, jnKwDoc

WHERE jnKwDoc.DocID = tblDoc.DocID

AND tblKeyW.KeyWID = jnKwDoc.KeyWID

 

the jnKwDoc holds a record containing each single keyword and the document ID.

 

I have no idea where to go from here.

 

 

[fenway]

Check out GROUP_CONCAT().

[mrsrowe]

Hello

 

thanks for that, but I really am a novice here and I don't understand which part of the query to use GROUP_CONCAT with in this query

 

SELECT * FROM tblDoc, tblKeyW, jnKwDoc

WHERE jnKwDoc.DocID = tblDoc.DocID

AND tblKeyW.KeyWID = jnKwDoc.KeyWID

 

 

[mrsrowe]

I'm using MySql MySql version 4.0.27, it appears not to work with versions earlier than 4.1

 

any other suggestions?

[fenway]

It's not that easily accomplished without GROUP_CONCAT().... I guess you could do it with user variables.