Hi.
On Sat, Feb 16, 2002 at 09:52:18AM -0800, [EMAIL PROTECTED] wrote:
> Thank you for your response.
> Sorry, about the incompleteness. I'll try to fill in some of the blanks.
Okay, let's see.
> Benjamin Pflugmann wrote:
[...]
> Yes, this is correct shifts is a is exactly like a did show shifts.
Fine.
> >Also, you did not specify what special cases are to be taken care
> >of. E.g. can there be volunteers which do not have a shift assigned
> >yet?
> >
> Yes, there can be some volunteers in the voluteers table that can
> have never been assigned a shift.
Okay, that differs from my presumption, but in every query except the
last (the one with LEFT JOIN) I already explained in my last mail how
to handle that case. And for the last one, you only need to append
AND NOT ( ISNULL(WHEN_YES) AND ISNULL(WHEN_NO) )
to the (last) WHERE clause.
> Althgough there will be future shifts, there are no shifts in the
> shifts table that have no voulnteers assigned to them.
This I don't understand. Above you say, the tables are like "did show
shift" and "no show shift". Then how can a shift be put in one of the
both tables, if it's in the future? Because it's in the future you
cannot say, which table would be the right one, can you?
But anyhow, it's only a matter of adding something like "WHEN <= NOW()"
to the appropriate WHERE clauses to ignore shifts with a date in the
future.
> >Can there be a shift in the future? And so on... I presume that
> >the tables reflect complete recordings _after_ each shift.
Here I wanted to make clear exactly this issue... that records can
only be added (reasonably) after a shift has passed.
> This is correct, the tables do refelet complete recordings
Well, we have some misunderstanding somewhere... or do you put future
shifts in the "shifts" table sometimes?
If the solution(s) don't work for you, please explain where you get
stuck, what result you get and what result you would expect.
Bye,
Benjamin.
PS: I just noticed that sometimes I had the query with "SELECT @volunteer"
too much. Just leave it away, if the following queries don't make
use of the user variable "@volunteer".
[...]
> >>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.
---------------------------------------------------------------------
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
