SELECT problem
-
Good morning
Two tables Funds and Accounts. Each fund in Funds has many account IDs associated with it:
Fund1
-
well ????
* Noel -
My mistake! Let me complete the post ...
Fund_ID Fund1 Acct1_ID Acct2_ID Acct3_ID
Fund_ID Fund2 Acct4_ID Acct5_ID Acct6_ID
and so on.
Accounts looks like this:
Acct_ID Acct_Number1
Acct_ID Acct_Number2
and so on.
How would I write a SELECT that would return:
Fund1 Acct_Number1 Acct_Number2 Acct_Number3
Fund2 Acct_Number4 Acct_Number5 Acct_Number6
Thanks!
jmatt
-
>>Fund_ID Fund1 Acct1_ID Acct2_ID Acct3_ID
This breaks normalization rules ... do you have control over the design & can you change it to be in normal form ?
As for the select, more info is required. Are there always 3 accounts per fund ? Can it be more than 3 ? If fixed at 3 can some/all of those denormalised Account ID columns contain NULLs and if so what is requried for NULL values ?
-
There always will be 3 accounts per fund and NULLS are not allowed for the accounts' IDs.
Each of these 3 accounts are for some default behavior for a particular fund (i.e. Cash Account for Revenue). How could this information be normalized?
I appreciate your help!
jmatt
-
Create Function dbo.fnGetAccountNumber(@AccountID int)
Returns BigInt
as
begin
declare @AccountNumber Bigint
set @AccountNumber = (SELECT AcctNumber FROM tempAccount WHERE Acct_id=@AccountID)
return @AccountNumber
end
SELECT f.Fundname,
dbo.fnGetAccountNumber(Acct1_ID) as Acct1,
dbo.fnGetAccountNumber(Acct2_ID) as Acct2,
dbo.fnGetAccountNumber(Acct3_ID) as Acct3
FROM funds f
inner join account a on f.acct1_id=a.acct_id
-
Sorry - that join needed work. Change your select to this:
SELECT f.Fundname,
dbo.fnGetAccountNumber(Acct1_ID) as Acct1,
dbo.fnGetAccountNumber(Acct2_ID) as Acct2,
dbo.fnGetAccountNumber(Acct3_ID) as Acct3
FROM tempfunds f inner join tempaccount a on f.acct1_id=a.acct_id
OR f.acct2_id=a.acct_id
OR f.acct3_id=a.acct_id
GROUP BY
Fundname,
dbo.fnGetAccountNumber(Acct1_ID),
dbo.fnGetAccountNumber(Acct2_ID),
dbo.fnGetAccountNumber(Acct3_ID)
-
Thanks! The user defined function route was just what I needed. They really are handy things!
jmatt
-
You don't need a GROUP BY if there are no aggregates, nor do you need the complexity of a user-defined function for what is just a simple relational join:
SELECT
f.Fund_ID,
a1.Acct_Number As Acct_Number1
a2.Acct_Number As Acct_Number2
a3.Acct_Number As Acct_Number3
FROM Funds As f
LEFT JOIN Accounts As a1
ON (f.Acct1_ID = a1.Acct_ID)
LEFT JOIN Accounts As a2
ON (f.Acct2_ID = a2.Acct_ID)
LEFT JOIN Accounts As a3
ON (f.Acct3_ID = a3.Acct_ID)
On the topic of normalization, table Funds has repeating elements of Acct_ID - capturing a 1:N relationship with repeating elements is not normalized. There should be 3 tables, Funds, Accounts and an intersect table FundAccount, which has 2 columns for Fund_ID and Acct_ID.
-
Because of the OR joins the GROUP BY was elliminating duplicates. I do like your solution without the function better though...
-
I also now see the light - no fuction required (but I still like them!). Thanks all very much for the help.
jmatt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply