MySQL Subquery with main query data variable

Question

Ok, need a MySQL guru here. I am trying to write a query that will serve as a notification system for when someone leaves a comment on an item that you have previously commented on. The 'drinkComment' table is very simple:

commentID, userID, drinkID, datetime, comment

I've written a query that will get all of the comments on drinks that I have previously commented on (that are not mine), but it will still show comments that occurred BEFORE my comment. This is as close to what I would think would work, but it does not. Please help!

select @drinkID:=drinkComments.drinkID, commentID, drinkID, userID, comment, datetime 
FROM drinkComments  
WHERE `drinkID` IN 
    ( select distinct drinkID from drinkComments where drinkComments.userID = 1) 
AND drinkComments.dateTime > (
/*This gets the last date user commented on the main query's drinkID*/
select datetime FROM drinkComments WHERE drinkComments.userID = 1 AND drinkComments.drinkID = @drinkID ORDER BY datetime DESC LIMIT 1
)
ORDER BY datetime DESC

Solution 1

Why not start with a prequery of the user and all the drinks they've offered comments and as of what time (don't know if you have multiple comments per person for any given drink or not). Then, find comments from all others AFTER such of your date/time comment...

This query should actually be faster as it is STARTING with only ONE USER's drink comments as a basis, THEN goes back to the comments table for those matching the drink ID and cutoff time.

SELECT STRAIGHT_JOIN
      dc.*
   from 
       ( select
               drinkID,
               max( datetime ) UserID_DrinkCommentTime
            FROM 
               drinkComments 
            WHERE
               userID = 1
            group by
               drinkID ) PreQuery
       join DrinkComments dc
         on PreQuery.DrinkID = dc.DrinkID
         and dc.datetime > PreQuery.UserID_DrinkCommentTime
   order by
      dc.DateTime desc

Solution 2

I think you need to relate your innermost query to the middle query by drinkID.

select @drinkID:=drinkComments.drinkID, commentID, drinkID, userID, comment, datetime 
FROM drinkComments  
WHERE `drinkID` IN 
    ( select distinct drinkID from drinkComments AS a where drinkComments.userID = 1) 
AND drinkComments.dateTime > (
/*This gets the last date user commented on the main query's drinkID*/
select datetime FROM drinkComments WHERE drinkComments.userID = 1 AND drinkComments.drinkID = a.drinkID ORDER BY datetime DESC LIMIT 1
)
ORDER BY datetime DESC