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
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
)
In case you need some back ground and want to know what I eventually
want to get read on...
I have a DB of Users of my wish list site. The tables I have are
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 am pretty sure I have to write code to do the last part with the
emails but is there a way to do the part with the gift and event counts?
--
Chris W
Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
