Re: Creating a view

Vern Reynolds wrote:
<snip previous discussion>
>
> I got your example to work by the way but with an exception. I would like to
> display the results of the lookup in four new columns in the view named
> ssopen, ssclose, wsopen and wsclose. As it is, is displays each lookup as a
> different row rather than all of them on one row.
>

Sorry - I just barged in on the error, not really paying attention to the model or motives.

basically, this should do the trick:
create view vern (ssopen, ssclose, wsopen, wsclose) as
SELECT TITLE, null, null, null from HYMNAL, SCHEDULE where PAGE=SSOPENINGHYMNNUMBER
union all
SELECT null,TITLE, null, null from HYMNAL, SCHEDULE where PAGE=SSCLOSINGHYMNNUMBER
union all
SELECT null,null,TITLE, null from HYMNAL, SCHEDULE where PAGE=WSOPENINGHYMNNUMBER
union all
SELECT null,null,null,TITLE from HYMNAL, SCHEDULE where PAGE=WSCLOSINGHYMNNUMBER;

This is not a pretty solution. Assuming schedule is an intersection table (creating the relation between two otherwise unrelated tables), what are the definitions of those tables?

Oh - and I haven't tested the above - I should have.

-- 
Regards, Frank van Bortel