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= > > >
