[thelist] one-to-many join problem
Brian Cummiskey
Brian at hondaswap.com
Sat Jan 14 18:51:49 CST 2006
Hi Matt, thanks for the quick response :)
Matt Warden wrote:
> I'm going to assume it is really set up like that (instead of
>waiting for an answer, since I know you're in a time crunch). So, I'm
>assuming your Phones table is like this:
>
>id confirmationcode longdistance dsl callerid
>1 1 true true false
>2 1 true false false
>
>
Yes, it's set up similar to this. instead of true or false, i am
storing the Plan name. I need to split them off of that too, but i can
take care of that later. For all puposes, what we have above will work
fine.
>So, you want a summary of each person and how many lines have long
>distance, dsl, and caller id.
>
>
More or less.
The data file I need to send has everything split out. For example,
there are 3 DSL packages:
DSL Extreme DSL Lite DSL Ultra
I need to denote if one was ordered:
1 0 0
In my table, like you have above, I have a field called dsl, and the
text value of the plan is stored there if ordered.
>This is tough because if you use count(), which is what one woudl think
>of first, it won't work, because you need count()s of records with
>different filters applied. You would need three queries in this case,
>one filtering on longdistance=true, another filtering on dsl=true, and
>another filtering on callerid=true.
>
>
exactly. and getting a group by on some 40 fields is not fun :P
>Using a trick I picked up from the SQL God Himself, Rudy Limeback, what
>(I think) you want can be accomplished.
>
>select m.fname, m.lname,
> sum(case when longdistance=true then 1 else 0 end) countld,
> sum(case when dsl=true then 1 else 0 end) countdsl,
> sum(case when callerid=true then 1 else 0 end) countcid
>from Main m, Phones p
>where m.confirmationcode=p.confirmationcode
>group by m.fname, m.lname
>
>
I think what i want to group on is the confirmation code though... no?
>your expanded recordset would look like this:
>
>joe guy 1 1 0
>joe guy 1 0 0
>
>and grouped you will have:
>
>joe guy 2 1 0
>
>which I think is what you want.
>
>
let me play a little more with this.
My code is very similar to what you have, only i'm joining with an
explicit left outer join.
i'll post back in a couple min :)
thanks for the help.
More information about the thelist
mailing list