Query syntax

Hi all! I am new here and I'm also new to SQL.. I hope somebody could help me regarding my problem.

I don't know if this is possible but I would like to have a code that

can integrate data from 3 tables. The names of my tables are Savings,

Loans and Insurances. Their common field is the MemberID. One member

could have zero or more Savings Accounts. At the same time, a member

could also have one or more accounts on Loans or Insurances.

How can I get the data that would appear like this:

MemberID - Savings Account - Loan Account - Insurance Account

0001

-

S0001

- L0002

- I0001

-

S0003

-

L0005

-

0002

-

S0012

-

-

0003

-

S0004

-

- I0002

-

-

- I0003
I'm using MS SQL Server 2005.

Hope you guys could help me with this. Thanks a lot, in advance!

what are the blank spaces for?

|||Thanks for replying...

I just would like to appear it that way, all accounts will be grouped

by members. I cannot use a subquery to return multiple values, right?

So when I implement it using UNION, it will somehow appear this way:

MemberID - Savings Account - Loan Account - Insurance Account

0001

-

S0001

-

-

0001

-

S0003

-

-

0001

-

-

L0002

-

0001

-

-

L0005

-

0001

-

-

- I0001

0002

-

S0012

-

-

0003

-

S0004

-

-

0003 -

-

- I0002

0003

-

-

- I0003
Or when I code it using the simplest Select query, all the records of

each account will be returned for each account of the member (or the

records will repeat for every account of the member).

Is there a tool or means in the 2005 version of MS SQL to do a query just like the one on the top?

Thanks a lot!|||

If there is a table that has all of the memberIds, it is a lot easier:

select memberId, savingAccount.number, loanAccount.number, insuranceAccount.number
from member
left outer join savingAccount
on member.memberId = savingAccount.memberId
left outer join loanAccount
on member.memberId = loanAccount.memberId
left outer join insuranceAccount
on member.memberId = insuranceAccount.memberId

If not (and there really really should be :) you can do something like:

select coalesce(savingAccount.memberId, loanAccount.memberId, insuranceAccount.memberId) as memberId,
savingAccount.number, loanAccount.number, insuranceAccount.number
from savingAccount
full outer join loanAccount
on savingAccount.memberId = loanAccount.memberId
full outer join insuranceAccount
on loanAccount.memberId = insuranceAccount.memberId

And this should do it too.

|||Yes, there is another table called Member but if I use the code above

with left join operator, the result set will be like the following:

(I will use only two tables for simplicity, Savings and Loans with memberId as their common column)

(and, suppose, one member has 2 savings number and 3 loans number)

Member.MemberID - Savings.No - Loans.No

M001

-

S001

- L001

M001

-

S001 -

L002

M001

- S001

-

L003

M001

-

S002

- L001

M001

- S002

- L002

M001

- S002

-

L003
If I desire it to look this way:

Member.MemberID - Savings.No - Loans.No

M001

- S001

-

L001

M001

- S002

-

L002

M001

- NULL

- L003

would it be possible?

If the code above with full outer join is used, the result set is still repeating values of each savings and loans.

I really appreciate your efforts. I hope I am not bothering you much.

Thanks!|||

Hi EDeric, you might have that requirement. but yor desired output is giving another relationship between Savings and Loans. Then what is the relationship between Savings and Loans?. is there any relationship between them?

|||Hi Uma!

It's only through MemberId column that the Savings and Loans have a relationship. Each table has that column.