SQL - Searching through a table, and associated comments table

[iainr]

Hi all,

 

I've come across a SQL statement I can't get right to get the right results.

 

I have two tables:

The first simply contains a list of "documents" - one record per document, and a title, description, etc.

The second is a list of comments, made by users, about these "documents" - some documents may have many comments, some may have zero. (a comment is tied to a document by the field "document_id")

 

What I have done so far is to search through the documents list for a search term, chosen by the user:

$document_search = mysql_query("SELECT * FROM documents_table 
WHERE (description LIKE '%$search_term%' OR subject LIKE '%$search_term%')")
or die(mysql_error());

 

What I am trying to do, is to also search through the associated comments, as well as the description and subject, and return that document if their key word is in either field, or any of the comments. My attempt:

$document_search = mysql_query("SELECT * FROM documents_table dt, comments_table ct
WHERE (dt.id = ct.document_id) AND
(dt.description LIKE '%$search_term%' OR dt.subject LIKE '%$search_term%' OR ct.comment LIKE '%$search_term%')")
or die(mysql_error());

Returns a record for every comment found (in some cases 5-6 records for the same document), and if a document has no comments, will not return any information about that document. (probably because of the dt.id=ct.document_id bit)

 

Any advice would be appreciated,

Cheers

 

[ignace]

SELECT dt.* FROM documents_table dt

LEFT JOIN comments_table ct ON dt.id = ct.document_id

WHERE (dt.description LIKE '%$search_term%' OR dt.subject LIKE '%$search_term%' OR ct.comment LIKE '%$search_term%')

[iainr]

Spot on, worked a treat! Then just had to add a DISTINCT in there to chop the results down,

 

Thanks for the help.