Subject Re: problem with left outer join
Author Adam
--- In, "Gary T. Benner" <gary@...>
> Hi all,
> I'd like some input on an issue with using left outer join's which
I use to retrieve data from tables where there may or may not be a
record in the joined tables.
> This is our query:
> select *
> from winetaste wt, wine w
> left outer join person p on p.personid = wt.tasterid
> left outer join region r on r.regionid = w.regionid
> left outer join country c on c.countryid = r.countryid
> left outer join winery y on y.wineryid = w.wineryid
> where wt.wineid = w.vintageid
> and wt.tasteid = 179008
> It fails with the error message "no current record for fetch
> The data is such that a "wine" record may not include region data,
yet I want to return as much data as is available from the other
> BTW you can tell what we drink here <g>.
> kind regards
> Gary

That is the error you get if you use a stored procedure in a join and
don't force the join order. Maybe it is getting mixed up with the
mixing of SQL 89 and 92?


select *
from winetaste wt
join wine w on (wt.wineid = w.vintageid)
left join person p on (p.personid = wt.tasterid)
left join region r on (r.regionid = w.regionid)
left join country c on (c.countryid = r.countryid)
left join winery y on (y.wineryid = w.wineryid)
where wt.tasteid = 179008


join = inner join
left join = left outer join