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? Bill On Thu, May 15, 2008 at 11:42 AM, Charles Parks <[EMAIL PROTECTED]> wrote: I still get the error "-ERROR- INTERNAL ERROR - in sort processing (2290)" with the revised syntax of select keyword from vwDVDKeywords where parentkeyword = 'fantasy' and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'adventure') and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'comedydrama') union select ChildKeyword from vwDVDKeywords where parentkeyword = 'fantasy' ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Stephen Markson Posted At: Thursday, May 15, 2008 9:58 AM Posted To: RB7-L Conversation: [RBASE-L] - RE: Select syntax Subject: [RBASE-L] - RE: Select syntax Try without DISTINCT Regards, Stephen Markson ForenSys The Forensic Systems Group www.ForenSys.ca <http://www.forensys.ca/> 416 512 6950 ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Charles Parks Sent: May 15, 2008 10:47 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Select syntax When I try a select statement I get "-ERROR- INTERNAL ERROR - in sort processing (2290)". The syntax I'm using is select distinct keyword from vwDVDKeywords where parentkeyword = 'fantasy' and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'adventure') and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'comedydrama') union select ChildKeyword from vwDVDKeywords where parentkeyword = 'fantasy' The individual statements work select distinct keyword from vwDVDKeywords where parentkeyword = 'fantasy' and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'adventure') and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'comedydrama') and select ChildKeyword from vwDVDKeywords where parentkeyword = 'fantasy' Keyword and ChildKeyword have the same datatype. What would be the correct way to union the two commands? No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 269.23.16/1434 - Release Date: 15/05/2008 7:24 AM No virus found in this outgoing message. Checked by AVG. Version: 7.5.524 / Virus Database: 269.23.16/1434 - Release Date: 15/05/2008 7:24 AM --- RBASE-L =======================3D======================3 D= TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected] (Don't use any of these words as your Subject: INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) =======================3D======================3 D= TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: INTRO =======================3D======================3 D= TO UNSUBSCRIBE: Send a plain text email to [email protected] In the message SUBJECT, put just one word: UNSUBSCRIBE =======================3D======================3 D= TO SEARCH ARCHIVES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: SEARCH-n (where n is the number of days). In the message body, place any text to search for. =======================3D======================3 D=
