MySQL Server Forum - SQL question

Author

SQL question

Chris W

2005-06-25, 3:23 am

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 thier 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

Bill Karwin

2005-06-26, 3:23 am

Chris W wrote:
> 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?

One trick I've seen suggested to do conditional counts is to select for
SUM(IF(e.Verified=1, 1, 0)) instead of using COUNT.

The part with the gift and event counts should be done by limiting the
groupings using a HAVING clause.

Regards,
Bill K.

Sponsored Links