[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