On 6/26/05, 2wsxdr5 wrote: > Can someone tell me why this query works... > SELECT UserKey > FROM( > SELECT UserKey, Count(GiftKey) Gifts > FROM Gift > Group BY UserKey > ) GC > WHERE GC.Gifts >= 3
Why this construction and not simply: SELECT UserKey FROM Gift GROUP BY UserKey HAVING Count(GiftKey) >= 3 > And this one doesn't? > > SELECT UserKey, UserID, > FROM User > WHERE UserKey IN > (SELECT UserKey > FROM( > SELECT UserKey, Count(GiftKey) Gifts > FROM Gift > Group BY UserKey > ) GC > WHERE GC.Gifts >= 3 > ) How do you mean "doesn't work"? Does it give an unexpected result or an error? > User ----> info about the users UserKey is the key > Gift ----> list of gifts each user has on their wish list foreign key > is UserKey > Event --->gift giving events for users. foreign key is UserKey > Emails ----> email addresses users have sent a message to about their > wish list. UserKey is the foreign key here too. > > The relationship between user and the other 3 tables is a 1 to many. I > have the following query that I need to adjust some. > > SELECT u.UserKey, UserID, > Count(distinct g.GiftKey) gifts, Count(distinct ev.EventKey) events, > Count(distinct e.Email) Emails > FROM User u NATURAL LEFT JOIN Gift g > LEFT JOIN Emails e ON e.Userkey = u.UserKey > LEFT JOIN GiftGivingEvent ev ON ev.UserKey = u.UserKey > GROUP BY UserID > > What I really want is only the users where the gifts count is > 3, the > Event count is > 1, the Emails count is > 5 and and only count emails > if e.Verified is = 1 I think you mean the following: SELECT * FROM User INNER JOIN ( SELECT UserKey, Count(UserKey) AS gifts FROM Gift GROUP BY UserKey HAVING Count(UserKey) > 3 ) USING (Userkey) INNER JOIN ( SELECT UserKey, Count(UserKey) AS events FROM Event GROUP BY UserKey HAVING Count(UserKey) > 1 ) USING (Userkey) INNER JOIN ( SELECT UserKey, Count(UserKey) AS emails FROM Emails WHERE Verified = 1 GROUP BY UserKey HAVING Count(UserKey) > 5 ) USING (Userkey) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
