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=
