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

DRapp · Accepted Answer · 2011-05-19 18:35:30Z

13

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

answered May 19, 2011 at 18:35

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

adamweeks Over a year ago

Very nice solution! Works and does perform faster! Thanks!

DRapp Over a year ago

@AdamWeeks, we try to offer efficient solutions when helping others... You can always change the "checked" answer to help future people get to the better solution than just the first.

The Thirsty Ape Over a year ago

+1 Great subquery tutorial. Did not realize i needed the subquery in the FROM section to access its results in the main query

Mr47 · Accepted Answer · 2011-05-19 18:13:17Z

1

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

answered May 19, 2011 at 18:13

Mr47

2,6651 gold badge20 silver badges26 bronze badges

1 Comment

adamweeks Over a year ago

That wasn't exactly it, but I modded it to work! Thanks so much! Made the first "FROM drinkComments" to "FROM drinkComments AS a" instead of the middle one.

Collectives™ on Stack Overflow

MySQL Subquery with main query data variable

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related