[Babase] query for marie
Susan Alberts
alberts at duke.edu
Fri May 11 09:02:23 EDT 2007
Thanks for the reply Karl -- see below:
>> Marie,
>> I think this query does it.
>
> Thanks Susan. I guess I was not focused on this yesterday.
No problem -- this only took me about 20 minutes. I think that it is
good in general for you to make decisions NOT to focus on things like
this when more important issues are on your plate -- kicking it back
into our arena is a good thing because it forces us to sharpen our
SQL skills. If we can't solve it, then it is appropriate for you to
get to it when you can. In other words, there should be a filter that
prevents you from doing low-level coding for us or even too much SQL
training. We should use you for harder problems, defined as problems
that several of us can't solve after a day of trying or so. Does this
make sense? See more below.
>
>> Main problem: The connection of members.date = cycpoints.date was
>> missing -- we only connected members.sname to cycles.sname. This
>> meant that every date in members was linking to every row in
>> cycpoints where sname matched and it returned thousands of extra
>> lines for every ddate.
>
> That is the real problem. We can return gobs of rows and then
> (I think) reduce them again using the DISTINCT keyword
> to get distinct cids and thus distinct cycles for counting
> purposes, but yours is really the right way to count Ddates.
>
>> Secondary problem: we selected on members.date instead of
>> cycpoints.date. I am not sure whether this was a problem or not
>> but on thinking about it I realized that the date in cycpoints was
>> the relevant date (it was the D date) and not the date in members.
>
> This is not a problem, so long as members.date = cycpoints.date. :-)
Thanks for this input -- I figured that this was the case, but it
seemed to me that the query would be more efficient if we selected on
cycpoints.date rather than members.date -- in other words, it might
run quicker. Is this right, or is it really not an issue?
>
>> select count(cycles.cid), members.grp,
>
> Note that with Susan's query you could count(*)
> to count how many rows are returned instead of count(cycles.cid).
>
Thanks,
Susan
More information about the Babase
mailing list