SQL: Introducing Having
I was asked recently to write the SQL needed to define a class method named User.busy? that would return an array of User records with a post count greater than or equal to 2.
Let's assume we have two tables, users and posts, linked by the primary key users.id and the foreign key posts.user_id.
I knew high level that I would need a join between the two tables and some way to apply a where clause on the count of posts grouped by users. A quick bit of research introduced me to the group by plus having technique.
1 select users.* from users join posts on posts.user_id = users.id group by posts.user_id having count(*) >= 2Ta da!
Comments (3)
Dad
May 08, 2010
4:41pm
Ok...so you think you're all that! On a serious note, I am most pleased by the fact that you have posted your solution to share with others who may have the same challenge. Proud of you!.....Dad
Dad
Aug 13, 2010
1:57am
Eric, Well, the weather continues to be hot and humid, yet your mother and I did our two mile walk together and shared some more quality time. Enjoyed having you down this past week. I'm still anticipating the lobster sandwich in NY....love Dad
Eric
Aug 13, 2010
2:00am
Looking forward to the cooler weather of Fall. It is blazing hot! I had two lobster rolls the other day that made me think of you. Always welcome to come up to NYC! Love you.
Leave a Comment