Hi Werner!

My first thought was that you seem to abuse LEFT JOIN, and that what
you are looking for is a UNION:

CREATE VIEW VRECIPEWFIT(
FK_RECIPEID,
FK_WINEFAMID,
FK_CELLARBOOKID,
FK_CBVINTAGEID,
FK_CBBOTTLEID)
AS
select reci.fk_recipeid, iwf.fk_winefamid, cb2.cellarbookid,
cbv2.cbvintageid, cbb2.cbbottleid
from winefam_ls wf
join ingrwfit iwf on wf.winefamid = iwf.fk_winefamid
join ingr_ls ingr on iwf.fk_ingrid = ingr.ingrid
join recipeit reci on ingr.ingrid = reci.fk_ingrid
join cellarbook cb2 on iwf.fk_winefamid = cb2.fk_winefamid
join cbvintage cbv2 on cb2.cellarbookid = cbv2.fk_cellarbookid
join cbbottle cbb2 on cbv2.cbvintageid = cbb2.fk_cbvintageid
union
select rec.recipeid, rwf.fk_winefamid, cb.cellarbookid,
cbv.cbvintageid, cbb.cbbottleid
from winefam_ls wf2
join recipewfit rwf on wf2.winefamid = rwf.fk_winefamid
join recipe rec on rwf.fk_recipeid = rec.recipeid
join cellarbook cb on rwf.fk_winefamid = cb.fk_winefamid
join cbvintage cbv on cb.cellarbookid = cbv.fk_cellarbookid
join cbbottle cbb on cbv.cbvintageid = cbb.fk_cbvintageid

This should also be easier for the optimizer.

Left joins are for those cases when you want records returned whether
or not you can join to the right table. That's why you got one (or
more) line(s) for each row in winefam_ls. Right joins are the same,
just that you switch which table is optional.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Werner F. Bruhin" wrote:
>
> The following creates nearly what I want. The reminding problem is
> that I get some "blank rows" which come from the winefam_ls table,
> i.e. I get at least one record writen to the view for that table,
> however I would only want to get a record writen to the view if a
> row is found in either the first group of tables and/or the second
> group. - I hope I am clear enough?
>
> I thought changing these two:
> left join ingrwfit iwf on wf.winefamid = iwf.fk_winefamid
> left join recipewfit rwf on wf.winefamid = rwf.fk_winefamid
> to:
> right join ingrwfit iwf on wf.winefamid = iwf.fk_winefamid
> right join recipewfit rwf on wf.winefamid = rwf.fk_winefamid
>
> I would very much appreciate if anyone can give me some pointers on
> what I am doing wrong.
>
> Best regards
> Werner
>
> CREATE VIEW VRECIPEWFIT(
> FK_RECIPEID,
> FK_WINEFAMID,
> FK_CELLARBOOKID,
> FK_CBVINTAGEID,
> FK_CBBOTTLEID)
> AS
> select coalesce(rec.recipeid, reci.fk_recipeid),
> coalesce(rwf.fk_winefamid, iwf.fk_winefamid),
> coalesce(cb.cellarbookid, cb2.cellarbookid),
> coalesce(cbv.cbvintageid, cbv2.cbvintageid),
> coalesce(cbb.cbbottleid, cbb2.cbbottleid)
>
> from winefam_ls wf
>
> /* First group */
> left join ingrwfit iwf on wf.winefamid = iwf.fk_winefamid
> left join ingr_ls ingr on iwf.fk_ingrid = ingr.ingrid
> left join recipeit reci on ingr.ingrid = reci.fk_ingrid
> left join cellarbook cb2 on iwf.fk_winefamid = cb2.fk_winefamid
> left join cbvintage cbv2 on cb2.cellarbookid = cbv2.fk_cellarbookid
> left join cbbottle cbb2 on cbv2.cbvintageid = cbb2.fk_cbvintageid
>
> /* Second group */
> left join recipewfit rwf on wf.winefamid = rwf.fk_winefamid
> left join recipe rec on rwf.fk_recipeid = rec.recipeid
> left join cellarbook cb on rwf.fk_winefamid = cb.fk_winefamid
> left join cbvintage cbv on cb.cellarbookid = cbv.fk_cellarbookid
> left join cbbottle cbb on cbv.cbvintageid = cbb.fk_cbvintageid