SQL question

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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
LEFT JOIN Emails e ON e.Userkey = u.UserKey
LEFT JOIN GiftGivingEvent ev ON ev.UserKey = u.UserKey

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

Re: SQL question

Chris W wrote:
Quoted text here. Click to load it

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.

Bill K.

Site Timeline