[thelist] one-to-many join problem
Brian Cummiskey
Brian at hondaswap.com
Sat Jan 14 18:11:37 CST 2006
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 :)
More information about the thelist
mailing list