My (sometimes muddled) understanding is that
A left outer join B inner join C
will return nothing if there is no B or C, when what I wanted was logically
A left outer join (B inner join C) that is - Give me an A regardless of B
or C
However I have been told that () have no effect therefore I just turn all
inners to outers if they follow an outer.
This has worked for me but it may not produce the desired results in all
situations.

Regards
Paul

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of RB Smissaert
> Sent: 28 September 2008 20:38
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] No current record for fetch operation
>
> I had a look at this:
>
>
>
> > I think that makes the LEFT JOIN actually be an INNER JOIN
>
>
>
> When I change the left join into an inner join I only get the
> unique field names and miss out on the duplicate field names
>
> due to one field being in more than one index. So, I think I
> need the SQL as it is and I can see no way to work it round
>
> so that the left join comes last.
>
> Maybe somebody can prove me wrong.
>
> Does the SQL run in FB 2.x is?
>
>
>
> RBS
>
>
>
> _____
>
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein
> Erling Tysvaer
> Sent: 28 September 2008 18:44
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] No current record for fetch operation
>
>
>
> Hi!
>
> I agree that Firebird (at least 1.5) poorly handles
> situations where LEFT JOIN is written before INNER JOIN.
> However, I find your particular SQL a bit contradictory:
>
> When doing INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE =
> T.RDB$TYPE), you basically say that there must be a record in
> both F and T, whereas you in the line before has said that F
> may not exist. I think that makes the LEFT JOIN actually be
> an INNER JOIN and in general I'd say it is wrong to use the
> right table of a LEFT JOIN in an INNER JOIN (*).
>
> That being said, the error message you report doesn't sound
> like a sensible error message for the situation and I think
> Firebird ought to be able to execute your query anyway (and I
> don't know whether this is what causes the error message or
> not). Though I don't know what causes the error, if it has to
> do with the dialect or something else.
>
> If your third line had contained a reference to RF and not F
> (thereby making the whole statement more sensible), I'd say
> it could be an error worth looking more into (note, I haven't
> checked any bug tracker, so it may already be a known issue).
>
> Set
>
> (*) I sometimes do similar things myself, but then when I
> know a record must exist in one of several tables:
>
> SELECT D.* FROM A
> LEFT JOIN B on A.A = B.A
> LEFT JOIN C on A.A = C.A
> INNER JOIN D on D.B = coalesce(B.B, C.B)
>
> RB Smissaert wrote:
> > Using FB 1.5 on a converted IB 5.6 dialect 1 database.
> > I connect with the ODBC driver from IBPhoenix and this is
> with ADO in VB6.
> >
> > When I run this SQL:
> >
> > SELECT
> > R.RDB$RELATION_ID,
> > RF.RDB$RELATION_NAME,
> > RF.RDB$FIELD_NAME,
> > T.RDB$TYPE_NAME,
> > RF.RDB$FIELD_POSITION,
> > F.RDB$FIELD_LENGTH,
> > RF.RDB$SYSTEM_FLAG
> > FROM
> > RDB$RELATION_FIELDS RF
> > LEFT JOIN RDB$FIELDS F ON (RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME)
> > INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE = T.RDB$TYPE)
> INNER JOIN
> > RDB$RELATIONS R ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)
>
> > WHERE
> > T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
> >
> > I get the error message:
> >
> > [ODBC Firebird driver][Firebird] No current record for
> fetch operation
> >
> > And I understand that this is caused by the left join before the 2
> > inner joins. Indeed if I change the left join to an inner
> join it runs fine.
> > There is no such problem with IB 5.6.
> >
> > It is not a major problem as I can do multiple selects in the
> > application and join in SQLite, but I just wonder if this has been
> > fixed in FB 2.0 or
> > 2.1 or has this to do with the database dialect?
> >
> >
> > RBS
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>