Charlie, You might want to review the difference between "UNION" and "UNION ALL". Without the keyword "ALL" UNION deletes duplicates. This requires and internal sort and could be a part of your problem.
You might also want to review the INNER JOIN syntax. Especially the ability to nest the INNER JOINS. I agree with your revised approach of creating a list if items you want in the "vwtest" view. "IN" and "NOT IN" as sub-selects are not the most efficient is processing. Without having actual data I can't immediately suggest alternative coding. INTEGERS are much more efficient to process so that's a good start. Jim Bentley --- Charles Parks <[EMAIL PROTECTED]> wrote: > Bill > > I will give that a try as a prelimanary test I tried removing > the two nested views from the view definition and making > changes to reference the Recno instead of the text values and > created these views: > > create view vwtest (KeyWordRecNo, ParentKeyWordRecno, > ChildKeywordRecNo) as SELECT keywordrecno,parentkeywordrecno, > childkeywordrecno FROM vwdvdkeyword2 WHERE parentkeywordrecno > = 1278 AND keyword NOT IN (SELECT keyword FROM vwdvdkeyword2 > WHERE parentkeywordrecno = 869) AND keyword NOT IN (SELECT > keyword FROM vwdvdkeyword2 WHERE > parentkeywordrecno = 7726) UNION SELECT > childkeywordrecno,null,null FROM vwdvdkeyword2 WHERE > parentkeywordrecno = 1278 > > CREATE VIEW `vwTest2` (KeyWordRecNo, ParentKeyWordRecno, > ChildKeywordRecNo, ParentKeyWord, ParentKeyWordRecNo, KeyWord, > KeyWordRecNo, ChildKeyWord, ChildKeyWordRecNo) AS SELECT > T1.KeyWordRecNo, T1.ParentKeyWordRecno, T1.ChildKeywordRecNo, > T2.ParentKeyWord, T2.ParentKeyWordRecNo, T4.KeyWord, > T4.KeyWordRecNo, T3.ChildKeyWord, T3.ChildKeyWordRecNo FROM > vwtest T1,vwParentKeyWords T2,KeyWords T4,vwChildKeyWord T3 > WHERE T1.KeyWordRecNo = T4.KeyWordRecNo AND > T1.ParentKeyWordRecno = > T2.ParentKeyWordRecNo AND T1.ChildKeywordRecNo = > T3.ChildKeyWordRecNo > > This generates some results. > > Charlie > ________________________________ > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of Bill Downall > Posted At: Thursday, May 15, 2008 2:04 PM > Posted To: RB7-L > Conversation: [RBASE-L] - RE: Select syntax > Subject: [RBASE-L] - RE: Select syntax > > > Charlie, > > When a query get that complicated, my inclination would be to > go to an incremental approach. Create a temporary table, > insert the rows from the first part of the query, and maybe > the last (union) part, then cycle through deleting the ones > that match the "not" subqueries, and present the result set in > whatever form you need to. > > Bill > > > On Thu, May 15, 2008 at 2:38 PM, Charles Parks <[EMAIL PROTECTED]> > wrote: > > > Is there a better way to write the select statement? > > > Here is a brief description of the tables. > DVD is my primary table. It has a title and will be > stored on a shelf. The row on the shelf is marked with a > GenreSectionRecNo inside of the database. > > Table: DVD No Lock(s) > No. Column Name Attributes > --- ------------------ > ------------------------------------------------------ > 1 GenreSectionRecNo Type : INTEGER > Consrnt: FOREIGN KEY REFERENCES > GenreSections > 2 DVDTitle Type : TEXT 100 NOT NULL > 3 IMDBCode Type : TEXT 100 > 4 DVDRecNo Type : INTEGER NOT NULL > AUTONUMBER > Consrnt: PRIMARY KEY > > Genres can have subgenres hence the ParentKeyWordRecNo > and ChildKeyWordRecNo in the table GenreSections. > > Table: GenreSections No Lock(s) > No. Column Name Attributes > --- ------------------ > ------------------------------------------------------ > 1 ParentKeyWordRecNo Type : INTEGER NOT NULL > Consrnt: UNIQUE > Consrnt: FOREIGN KEY REFERENCES > KeyWords > 2 ChildKeyWordRecNo Type : INTEGER NOT NULL > Consrnt: UNIQUE > Consrnt: FOREIGN KEY REFERENCES > KeyWords > 3 GenreSectionRecNo Type : INTEGER NOT NULL > AUTONUMBER > Consrnt: PRIMARY KEY REFERENCED > 4 ParenSortNo Type : INTEGER > 5 ChildSortNo Type : INTEGER > > > Each movies has plot elements or keywords. > > Table: KeyWords No Lock(s) > No. Column Name Attributes > --- ------------------ > ------------------------------------------------------ > 1 KeyWord Type : TEXT 100 > 2 KeyWordRecNo Type : INTEGER NOT NULL > AUTONUMBER > Consrnt: PRIMARY KEY REFERENCED > > The relationship of DVDs to keywords is many-to-many. > > Table: DVDKeyWords No Lock(s) > No. Column Name Attributes > --- ------------------ > ------------------------------------------------------ > 1 DVDRecNo Type : INTEGER NOT NULL > Consrnt: UNIQUE > 2 KeywordRecNo Type : INTEGER NOT NULL > Consrnt: UNIQUE > 3 DVDKeyWordRecNo Type : INTEGER NOT NULL > AUTONUMBER > Consrnt: PRIMARY KEY > > Views vwparentkeywords and vwchildkeyword attempt to > translate the record number into a valid word. > > > > ________________________________ > > From: [email protected] [mailto:[EMAIL PROTECTED] On > Behalf Of Bill Downall > > Posted At: Thursday, May 15, 2008 12:23 PM > > Posted To: RB7-L > Conversation: [RBASE-L] - RE: Select syntax > Subject: [RBASE-L] - RE: Select syntax > > > > Charlie, > > I tried to follow all that, but I suffered an internal > sort error. > > > Bill > > > > On Thu, May 15, 2008 at 12:25 PM, Charles Parks > <[EMAIL PROTECTED]> wrote: > > > The definition is > CREATE VIEW `vwDVDKeywords` (DVDTitle, > Keyword, DVDKeywordRecNo, DVDRecNo, KeywordRecNo, > GenreSectionRecNo, ParentKeyWord, ParentKeywordRecno, > ChildKeyWord, ChildKeyWordRecNo) AS SELECT > t1.DVDTitle,t3.keyword,t2.dvdkeywordrecno, t1.DVDRecNo, > t3.keywordrecno, t4.genresectionrecno, t5.ParentKeyWord, > ParentKeywordRecno, t6.ChildKeyWord, ChildKeyWordRecno FROM > DVD t1,keywords t3,dvdkeywords t2,genresections > t4,vwparentkeywords t5,vwchildkeyword t6 WHERE t3.keywordrecno > = t2.keywordrecno AND t1.dvdrecno = t2.DVDRecNo AND > t4.genresectionrecno = t1.genresectionrecno AND > t4.parentkeywordrecno = t5.parentkeywordrecno AND > t4.parentkeywordrecno = t5.parentkeywordrecno AND > t4.childkeywordrecno = t6.childkeywordrecno > > > the definition of the two nested views are > CREATE VIEW `vwParentKeyWords` AS SELECT > DISTINCT t1.KeyWord AS ParentKeyWord,t1.KeyWordRecNo AS > ParentKeyWordRecNo FROM KEYWORDS t1,GenreSections t2 WHERE > t1.KeyWordRecNo = t2.ParentKeyWordRecNo > > CREATE VIEW `vwChildKeyWord` AS SELECT > t1.KeyWord AS ChildKeyWord, t1.KeyWordRecNo AS > ChildKeyWordRecNo FROM KEYWORDS t1,GenreSections t2 WHERE > t1.KeyWordRecNo = t2.ChildKeyWordRecNo > > ________________________________ > > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of Bill Downall > Posted At: Thursday, May 15, 2008 10:46 AM > > Posted To: RB7-L > Conversation: [RBASE-L] - RE: Select syntax > Subject: [RBASE-L] - RE: Select syntax > > > Charles, > > What is the definition of the view > vwDVDKeywords? > === message truncated === Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293
