Hi Svein,

Svein Erling Tysvær wrote:

> Hi Werner!
>
> My first thought was that you seem to abuse LEFT JOIN,

I think I abused a lot more over the last two days then just LEFT JOIN!
and that what

> you are looking for is a UNION:

I was aware of UNION but did not really know how to use it, thanks a lot
for having taken the time to put me on the right path.

It definitely looks like this is working, will have to add a third group
to it and do some more testing.

Thanks again
Werner

>
> 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
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net 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
>
>
>
>
>
>