[thelist] one-to-many join problem

Matt Warden mwarden at gmail.com
Sat Jan 14 18:35:11 CST 2006

Hash: SHA1

Brian Cummiskey wrote:
> for example,
> Joe has 2 phone numbers, each with long distance options, but only 1 
> with dsl.
> so i have:
> Main:
> row1 Joe codeA
> Phones:
> row1 codeA longdistance  DSL
> row2 codeA longdistance
> what i need for the output is:
> Joe codeA 2 1

Is your database really set up like that, or is that just the example
data? 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

So, you want a summary of each person and how many lines have long
distance, dsl, and caller id.

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.

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

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.

Hope this helps. Good luck with the project.

- --
Matt Warden
Miami University
Oxford, OH, USA

This email proudly and graciously contributes to entropy.
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org


More information about the thelist mailing list