[thelist] one-to-many join problem

[Brian Cummiskey]

Hi All-

I'm working overtime trying to finish a project for go-live on monday 
monring.  Of course, I get stuck...  lol

Here's what I'm dealing with:

I have 2 tables I need to export an "order" from for a phone company.

Per order, there can be multiple phone numbers/lines.  Each line can 
have its own options for long distance, etc, so they must be stored in a 
seperate table.


Tables are related by a confirmation code number, one to many (main 
table to phone numbers table)

I'm trying to set up an output, so that i get 1 record that includes the 
counts from the main table.

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

I'm having problems getting everything on 1 row.  My join is giving me 
all the data from the main table for each line.  What i really need is 
just the counts of the options.

database is ms sql 2000.

I guess the main problem i'm facing is that i don't know how to attack 
this. 


TIA :)