[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 

>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