MySQL reference row in sub query

Question

I'm trying to count all messages sent by users AFTER they uploaded a photo.

I'm trying something like this.

select messages.created_at, count(*) as count from messages 
      inner join users on messages.user_id = users.id
      inner join photos on photos.user_id = users.id
      where 
        some_users_messages.created_at > some_users_first_photo.created_at
      group by YEARWEEK(messages.created_at)

I'm thinking this needs to be a subquery? I'm not sure how to do this concept of one particular user's messages/photos in MySQL. Any ideas?

Thanks!

Are you trying to return a list of users and their message count? Or a total regardless of users? Or a message count for one user? — Jage
– Jage, Commented Jul 1, 2011 at 22:38

Andomar · Accepted Answer · 2011-07-01 22:39:47Z

2

This would count the number of messages sent after the first photo per user:

select  messages.user_id
,       count(*) as count 
from    messages 
where   messages.created_at >
        (
        select  min(created_at)
        from    photos
        where   photos.user_id = messages.user_id
        )
group by 
        messages.user_id

answered Jul 1, 2011 at 22:39

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

1 Comment

Brian Armstrong Over a year ago

Awesome, that was easier than I thought. Thanks!

Collectives™ on Stack Overflow

MySQL reference row in sub query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related