Reducing the recordset where a particular field has similar data. (MySQL)

Question

I have two tables, 'discussion' and 'discussion_responses'. A unique ID from 'discussion' is used in 'discussion_responses' to identify the response to the original post in a forum. I created the following query to extract the posts that have NEW replies, but are NOT linked to new topics. Both tables have a field for the date added and 'colname' is Dreamweaver's variable name for the visitor's last log-in date retrieved from another table:

SELECT *
FROM discussion, discussion_responses
WHERE discussion.discussion_date < colname 
AND discussion_responses.discussion_date > colname 
AND discussion.discussion_ID = discussion_responses.discussion_ID
ORDER BY discussion.discussion_title ASC

My problem is that where more than one reply has been made to the original post, I naturally get more than one result for that discussion ID. Can anyone please advise me as to how I can eliminate subsequent rows containing the same discussion_ID? It doesn't matter which record is chosen, only that I get the ID of any topic that has had a new reply associated with it. Thanks.

Answer 1

If you're just interested in the discussion_ID, you could use distinct or group by. Here's an example with distinct:

SELECT      DISTINCT discussion_ID
FROM        discussion d
INNER JOIN  discussion_responses dr
ON          d.discussion_ID = dr.discussion_ID
WHERE       d.discussion_date < colname 
AND         dr.discussion_date > colname 

If you're also interested in other columns, filter on the distinct id's in a subquery:

SELECT      *
FROM        discussion d
WHERE       d.discussion_ID IN (
    <query from above here>
)

Answer 2

This:

SELECT  *
FROM    discussion d
WHERE   discussion_date < colname 
        AND EXISTS
        (
        SELECT  NULL
        FROM    discussion_responses dr
        WHERE   dr.discussion_date > colname 
                AND dr.discussion_ID = d.discussion_ID
        )
ORDER BY
        d.discussion_title ASC

or this:

SELECT  d.*
FROM    (
        SELECT  DISTINCT discussion_ID
        FROM    discussion_responses dr
        WHERE   dr.discussion_date > colname
        )
JOIN    discussion d
ON      d.discussion_ID = dr.discussion_ID
WHERE   d.discussion_date < colname 
ORDER BY
        d.discussion_title ASC

The latter query will probably be faster, since the condition used in the second query's leading table (discussion_responses) is more selective.

Create an index on discussion_responses (discussion_date, discussion_id).