I've two tables, 'discussion' and 'discussion_responses'. A distinctive ID from 'discussion' can be used in 'discussion_responses' to recognize the reaction to the initial publish inside a forum. I produced the next query to extract the posts which have NEW replies, but aren't associated with new subjects. Both tables possess a area for that date added and 'colname' is Dreamweaver's variable reputation 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 issue is that where several reply has been manufactured towards the original publish, I naturally get several result for your discussion ID. Can anybody please advise me regarding the way i can eliminate subsequent rows that contains exactly the same discussion_ID? It does not matter which record is selected, that I recieve the ID associated with a subject which has were built with a new reply connected by using it. Thanks.

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 second query will most likely be faster, because the condition utilized in the 2nd query's leading table (discussion_responses) is much more selective.

Create a catalog on discussion_responses (discussion_date, discussion_id).

If you are just thinking about the discussion_ID, you could utilize distinct or group by. Here's a good 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 are also thinking about other posts, filter around the distinct id's inside a subquery:

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