Maybe this could work ...

select N.Name, L.LendDate
from Names N
left outer Join Lend L on (L.UserId = N.UserId and L.BookId = 100)

----- Original Message -----
From: andrew_s_vaz
To: firebird-support@yahoogroups.com
Sent: Friday, November 26, 2004 8:38 AM
Subject: [firebird-support] Outer Join Problem



Hi all,

I've read a lot articles about joins but I'm stuck with a
Left Outer Join Problem. As I read in Ann's post (11/16/04
Sql Query Question):

<< select t1.f1, t2.f1, t1.f3
from table1 t1
left outer join table2 t2 (on t2.f3=t1.f1)
where t1.f4 = 6

If you don't want rows from t1 that don't have matching rows
in t2, leave out the "left outer" and give the query optimizer
something more to work on.>>

I'm trying to do something slightly diferent. Given a table
with people's names and books lended (like in a Library) I want
to make a list with all the names but only show a specified book.

Something like this:

Show all names and who borrowed The Bible.

Names LendDate
===== ====
Peter 11/10/04
Tom
Joe
Paul

Obviously there are other data with the book info, but I can't
get even the basic stuff.

I tried:

=======
select N.UserId, N.Name, L.LendDate, L.BookId

from Names N
left outer Join Lend L on (L.UserId = N.UserId)

Where L.BookId = 100
========

but I only retrieve 1 row with the user that lended the book,
not all the users as I need. What am I doing wrong?

I'm using Firebird 1.5.1.

(in case of asking why, I'm migrating from another db and it's
a report that exists on the old app.)

Thanks in advance.
--
Andrew

(sorry if shows up twice, I tried to post it with my SuperGravity
Newsreader and It didn't show up in more or less an hour, so I posted
it again directly in Yahoo.)




Yahoo! Groups Sponsor
ADVERTISEMENT





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]