[Resolved]Is the DataBase Design correct ?

[GOBI]

Hi i have a problem in writing a query , here goes the problem .
I have a table structure like this

[Loan TABLE]
loanId
loandate
returnId
returndate

[User TABLE]
ID
NAME

1)loan.loadid has a relationship with user.id
2)loan.returnid also has a relationship with user.id

Well because the loanid and returnid has a relationship with user.id , is it possible to create a join to display loanid and returnid with respect to name ?
e.g.
[loan table]
loanid loandate returnid returndate
1 monday 3 monday
2 tuesday 1 monday
[user table]
id name
1 alan
2 charlie
3 daniel

Below is the result i which to get
[result]
alan monday daniel monday
charlie tuesday alan monday

[What i have tried]
select user.name , loandate , user.name,returndate from loan inner join user on loan.loanid=user.id and loan.returnid=user.id

[szlamany]

Try this:

Select * From [Loan Table] LT
 Left Join [User Table] U1 on U1.Id=LT.LoanId
 Left Join [User Table] U2 on U2.Id=LT.ReturnId

[FunkyDexter]

Yes, you simply join to two different versions of the user table.

Select Loaner.Name, Loan.LoanDate, Returner.Name, Loan.ReturnDate
From Loan
Inner join user as Loaner
   on Loan.LoanID = User.ID
Inner join user as Returner
   on Loan.ReturnID = User.ID

You probably don't want a table name of user though as it's almost certainly a reserved word

edit - damn, beaten to the punch

[szlamany]

I agree!

even worse to have those spaces in the table names - although the []'s will work...

I've got a link in my sig to a list of reserved words that should be avoided.

btw - INNER JOIN will eliminate the LOAN record is the USERID do not join to a valid user row - just wanted to clarify that.

[GOBI]

Thanks and appreciate the help from both of you