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.
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
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> )