Richard,
> I'm stumped -- which isn't saying much because I'm hardly a scientist.
> I am struggling to write a query that will tell me how many times a new
> volunteer ( defined as a volunteer who has never worked a shift ) did
> not show up for his shift (first shift). From my data I would
> eventually like to be able to say: a brand new volunteer does not show
> up for his shift 1 in x times.
>
> I have three tables:
>
> volunteer
> VID | Name
> -----------------
> 524 | Joe Doe
> 254 | Karen Smith
> 485 | Bob Nesbit
>
> shifts
> SID | DATE | VID
> ---------------------------
> 230 | 2000-01-28 | 584
> 231 | 2000-02-01 | 485
> 233 | 2000-02-03 |147
> 234 | 2000-02-04 | 584
>
> no_show_shifts
> SID | DATE | VID
> ---------------------------
> 232 | 2000-02-01 | 259
> 239 | 2000-02-08 | 369
>
>
> Is it possible to write a query that will solve this problem? If so,
> can anyone tell me how I can do it?
> If not what other data do I need? Any help is appreciated.
You're stumped!? Where does that leave us? Unfortunately (AFAIK) the sample data you
have enclosed does not
demonstrate the condition you seek to describe - perhaps it would have been better if
you gave us a replicable
example, so that we can UNDERSTAND the problem before we try to help you out. Neither
did you enclose your
current best-shot so that we might see how you have approached the problem (and
perhaps further 'discover'
aspects of the problem) [grumble!]
Forgive me for ignoring the finer points of your requirement, in an attempt to
actually get the 'right data'
coming off the tables first...What does the following query do for you?
SELECT volunteer.VID, volunteer.Name, no_show_shifts.SID, no_show_shifts.DATE,
shifts.SID, shifts.DATE, count()
FROM volunteer, no_show_shifts, shifts
WHERE no_show_shifts.VID = volunteer.VID
AND shifts.VID = volunteer.VID
GROUP BY volunteer.VID
HAVING no_show_shifts.DATE < shifts.DATE
Does the COUNT() have to mention no_show_shifts.DATE or .SID?
Please advise,
=dn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
