Complicated join query - how is this done?

At least this is complicated for me. I hope it's not trivial for you
experts!
Accounts table has Acct_Number (unique), Client_ID, and Rep_Num. The
rep num is the number that identifies the registered representative(s)
on the account. (The client ID, of course, points to the client's name,
address, and other stuff in another table.)
The Rep_Nums table has Rep_Num (not unique) and Rep_ID. Each rep number
can map to many rep IDs. Many acccounts are assigned to a "split rep"
number where two (or more) individual "rep persons" share the work of
servicing the account and share the commissions.
The Rep_Names table has Rep_ID (unique), Rep_FName, and Rep_LName with
the rep-person's first and last name.
So far, straightforward.
Now, when we display account information, we usually don't need to see
the names of all reps assigned to the rep number on the account -- any
one rep-person's first and last name is enough information for internal
display purposes (in case we need to call one of the reps on an account
for some reason).
Now I figured out one way to do this -- I created a Rep_View that groups
the rep_nums table by rep number, and picks the smallest rep_id for each
one, and returns that rep's first and last name.
So when I want to display account information along with the name of one
of the reps on the account, I use the rep view instead of the underlying
tables.
But::: The rep name information comes from external data sources that
we have no control over, and the rep's first and last names are null for
many of the rep_IDs in the rep_names table. We're trying to fill in the
missing information, but it's a slow process, and in the meantime, we
need to show reports.
Of course, sometimes a rep number points to two rep IDs, and one of
those rep IDs has a first and last name in the table and the other does
not.
You might see where this is leading -- I need to display account
numbers, client IDs (I'll get the client names from another table), rep
number, and ONE of the first name/last name pairs from the rep names
table, picking any rep first name/last name pair that's not null for the
rep number.
How is this done? The only way I can think of involves creating an on-
the-fly computed column that holds rep first name concatenated with rep
last name, and pick the min or the max of that (for each rep number) to
try to get a non-null rep name pair. Then I might want to un-
concatenate that rep's name to get back to separate rep first and last
name, and that's messy.
And I don't like creating on-the-fly computed columns that concatenate
varchar fields.
Of course, all sets of rep first name/rep last names for any rep number
might be null too, and if so, we need to show null or "Unknown" for the
rep's name.
Comments are appreciated!
David WalkerSelect Acct_Number, Client_ID,
Min(Rep_FName + ' ' + Rep_LName) RepName
From Accounts A
Left Join (Rep_Nums N Join Rep_Names R
On R.Rep_ID = N.Rep_ID)
On N.Rep_Num = A.Rep_Num
And R.Rep_FName Is Not Null
And R.Rep_LName Is Not Null
"DWalker" wrote:

> At least this is complicated for me. I hope it's not trivial for you
> experts!
> Accounts table has Acct_Number (unique), Client_ID, and Rep_Num. The
> rep num is the number that identifies the registered representative(s)
> on the account. (The client ID, of course, points to the client's name,
> address, and other stuff in another table.)
> The Rep_Nums table has Rep_Num (not unique) and Rep_ID. Each rep number
> can map to many rep IDs. Many acccounts are assigned to a "split rep"
> number where two (or more) individual "rep persons" share the work of
> servicing the account and share the commissions.
> The Rep_Names table has Rep_ID (unique), Rep_FName, and Rep_LName with
> the rep-person's first and last name.
> So far, straightforward.
> Now, when we display account information, we usually don't need to see
> the names of all reps assigned to the rep number on the account -- any
> one rep-person's first and last name is enough information for internal
> display purposes (in case we need to call one of the reps on an account
> for some reason).
> Now I figured out one way to do this -- I created a Rep_View that groups
> the rep_nums table by rep number, and picks the smallest rep_id for each
> one, and returns that rep's first and last name.
> So when I want to display account information along with the name of one
> of the reps on the account, I use the rep view instead of the underlying
> tables.
> But::: The rep name information comes from external data sources that
> we have no control over, and the rep's first and last names are null for
> many of the rep_IDs in the rep_names table. We're trying to fill in the
> missing information, but it's a slow process, and in the meantime, we
> need to show reports.
> Of course, sometimes a rep number points to two rep IDs, and one of
> those rep IDs has a first and last name in the table and the other does
> not.
> You might see where this is leading -- I need to display account
> numbers, client IDs (I'll get the client names from another table), rep
> number, and ONE of the first name/last name pairs from the rep names
> table, picking any rep first name/last name pair that's not null for the
> rep number.
> How is this done? The only way I can think of involves creating an on-
> the-fly computed column that holds rep first name concatenated with rep
> last name, and pick the min or the max of that (for each rep number) to
> try to get a non-null rep name pair. Then I might want to un-
> concatenate that rep's name to get back to separate rep first and last
> name, and that's messy.
> And I don't like creating on-the-fly computed columns that concatenate
> varchar fields.
> Of course, all sets of rep first name/rep last names for any rep number
> might be null too, and if so, we need to show null or "Unknown" for the
> rep's name.
> Comments are appreciated!
>
> David Walker
>|||David,
You have posted here several times, right? How about following
www.aspfaq.com/5006?
Anith|||"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:D58B52E9-D083-4510-86CF-986F3981F7B5@.microsoft.com:

> Select Acct_Number, Client_ID,
> Min(Rep_FName + ' ' + Rep_LName) RepName
> From Accounts A
> Left Join (Rep_Nums N Join Rep_Names R
> On R.Rep_ID = N.Rep_ID)
> On N.Rep_Num = A.Rep_Num
> And R.Rep_FName Is Not Null
> And R.Rep_LName Is Not Null
>
That helps, thanks. I still need to get the rep names out in separate
First and Last name fields; I can put a delimiter other than space in
there and split them again, I suppose.
Thanks.
David Walker
> "DWalker" wrote:
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in news:OT91gVpJFHA.2980
@.TK2MSFTNGP10.phx.gbl:

> David,
> You have posted here several times, right? How about following
> www.aspfaq.com/5006?
>
Sorry, I meant to add the DDL at the bottom, but I simplified some of the
fields (there are lots of fields that don't contribute to the problem) and
I was looking for techniques. I'll post the DDL next time.
David Walker|||"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:D58B52E9-D083-4510-86CF-986F3981F7B5@.microsoft.com:

> Select Acct_Number, Client_ID,
> Min(Rep_FName + ' ' + Rep_LName) RepName
> From Accounts A
> Left Join (Rep_Nums N Join Rep_Names R
> On R.Rep_ID = N.Rep_ID)
> On N.Rep_Num = A.Rep_Num
> And R.Rep_FName Is Not Null
> And R.Rep_LName Is Not Null
That doesn't work.
Column 'Accounts.ACCT_NUMBER' is invalid in the select list because it is
not contained in an aggregate function and there is no GROUP BY clause.
And the same message for Client_ID.
I see that you're trying to pick one of the advisor names where they are
not null, but without grouping, it doesn't work.
David|||On Fri, 18 Mar 2005 09:41:32 -0800, DWalker wrote:

>"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
>news:D58B52E9-D083-4510-86CF-986F3981F7B5@.microsoft.com:
>
>That doesn't work.
>Column 'Accounts.ACCT_NUMBER' is invalid in the select list because it is
>not contained in an aggregate function and there is no GROUP BY clause.
>And the same message for Client_ID.
>I see that you're trying to pick one of the advisor names where they are
>not null, but without grouping, it doesn't work.
>David
Hi David,
I think CBretana intended to add
GROUP BY Acct_Number, Client_ID
at the end of the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo.
David
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
news:87cm31dchmap2uano4sab114ba0fu36thq@.
4ax.com:

> On Fri, 18 Mar 2005 09:41:32 -0800, DWalker wrote:
>
> Hi David,
> I think CBretana intended to add
> GROUP BY Acct_Number, Client_ID
> at the end of the query.
> Best, Hugo