SQL question regarding searching for data from multiple tables

Question

I have 2 tables containing information that i want to search for, a main table, and a comments table. The main table contains timestamps, subjects, etc. While the comments table holds comments for the individual records in the main table. Its basically a simple ticket system.

I need to be able to search for things in the main table and the comments table in the same query. Here is the query I have now:

SELECT DISTINCT d.* FROM ticket_data d, ticket_comment c WHERE
      (
       d.subject LIKE '%test%' OR
       d.message LIKE '%test%' OR
       c.comment LIKE '%test%'
      )
   AND c.tid = d.id

This works great for tickets that have comments (c.tid) but if no comments are available, no results are returned. I know this is due to the c.tid = d.id part of the query, but I don't know how to connect the comments with the main without doing that.

Thanks for any help or suggestions on how to make this query better!

Answer 1

Try using a left outer join on the two tables.

SELECT DISTINCT d.* FROM ticket_data d 
LEFT OUTER JOIN ticket_comment c on c.tid = d.id WHERE
  (
   d.subject LIKE '%test%' OR
   d.message LIKE '%test%' OR
   c.comment LIKE '%test%'
  )

Answer 2

To get the a record, even when the paired record does not exist, you need to use a left outer join. For example:

FROM
    ticket_data d 
    LEFT JOIN
    ticket_comment c ON c.tid = d.id