Instead of not in (select...) syntax you can try using this syntax: not (exists (select...) and exists (select...)). Exists or not exists looks for a single instance of a row in the sub-query. When queries aren't running efficiently I sometimes substitute exists for "in" when trying to optimize.
Regards, Ken ----- Original Message ----- From: Charles Parks Date: Thursday, May 15, 2008 4:56 pm Subject: [RBASE-L] - RE: Select syntax To: [email protected] (RBASE-L Mailing List) > 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 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 > 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:rbase- > [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 > 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:rbase- > [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 > > 416 512 6950 > > > > ________________________________ > > From: [email protected] [mailto:rbase- > [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= > > >
