Another Tricky QUERY

[ambros]

Can anyone help with this...
I have three tables, Users, Books, BookLoans (for a library database.)
I need to print a report on All Users whether they have a book on loan or not. The following gives me anyone that has a book out but not anyone who doesn't.

SELECT BOOKLOANS.Title, UERS.USERID#, sbrady_case2_USERS.UserName
FROM BOOKLOANS, USERS
WHERE BOOKLOANS.UserID#= .UserID#;

What I keep getting is that if someone doesn't have a book out it prints there name whatever number of times there is to the number of books on loan.
I want it to print something like 'null'. As in this person has no book on loan. That make sense?
I've tried my best but to no avail.
Any help would be appriciated thanks

ambrose

 

[sagn]

If I understand correctly what you want
I think an Outer join should do the trick


select a.user,b.books

from users a

left outer join books b
on
a.uid = b.uid

This would list all the users even if they do not
have an entry in the books table...

 

[bperry]

>>I need to print a report on All Users

Here's another guess. (untested)

Select Col1, Col2, Col3
From USERS
Left Join BOOKLOANS
ON USERS.USERID# = BOOKLOANS.USERID#
Left Join BOOKS
ON BOOKLOANS.TitleId = BOOKS.TitleId