Adding different columns from different table

I have three tables.

Member(name, address, ID)

Loan(ID, startdate, amount)

Deposite(ID, startdate, amount)

I wanna create a report which look like this.

ID MembersName startdate address etc

Member can be either borrower or a depositor.

I'm thinking of using inner join. Can anyone help me to write the query?

Thanks

*If* ID links all three tables then the query would look like this:

This query is based on a member being *Either* a borrower OR a depositor.

Code Snippet

Select

m.ID,

M.Name,

Coalesce(L.StartDate, D.StartDate) as StartDate,

m.Address

From Member m (nolock)

left outer join Loan L (nolock)

on m.ID = L.ID

left outer join Deposit D (nolock)

on m.ID = D.ID

If a member can be a borrower AND/OR a depositor, the query would look like this:

Code Snippet

Select

m.ID,

S.AccountType,

m.Name,

S.StartDate,

m.Address

From

Member m (nolock),

inner join

(Select

ID,

StartDate,

'Loan' as AccountType

From

Loan l (nolock)

union

Select

ID,

StartDate,

'Deposit' as AccountType

From

Deposit d (nolock)

) S

on

m.ID = S.ID

Order by ID

You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.

HtH

BobP

|||

Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.

As a example,

If the start date of one perticular member is in borrower table , then he is a borrower.

Can you please help me to write this query too?I really appriciate

Thanks

|||

Actually, the 2nd query I have above will show that. I included a column for member type.

BobP