Re: SQL Question... Boolean Logic

In article <mike_schuler.8.0009FB9C_at_mindlink.bc.ca> mike_schuler_at_mindlink.bc.ca (Mike Schuler) writes:
>Path: news.mindlink.net!line39.nwm.mindlink.net!mike_schuler
>From: mike_schuler_at_mindlink.bc.ca (Mike Schuler)
>Newsgroups: comp.databases.oracle
>Subject: Re: SQL Question... Boolean Logic
>Date: Tue, 25 Apr 1995 09:58:54
>Organization: IBX datasystems limited
>Lines: 73
>Message-ID: <mike_schuler.8.0009FB9C_at_mindlink.bc.ca>
>References: <1995Apr24.120235.6502_at_newton.ccs.tuns.ca>
>NNTP-Posting-Host: line39.nwm.mindlink.net
>X-Newsreader: Trumpet for Windows [Version 1.0 Rev A]

>In article <1995Apr24.120235.6502_at_newton.ccs.tuns.ca> James Richard writes:
>>Newsgroups: comp.databases.oracle
>>Path:
>>news.mindlink.net!vanbc.wimsey.com!scipio.cyberstore.ca!math.ohio-state.edu!cae
 n
>>!zip.eecs.umich.edu!newshost.marcam.com!uunet!in1.uu.net!newsflash.concordia.ca
 !
>>nstn.ns.ca!newton.ccs.tuns.ca!news
>>From: James Richard
>>Subject: Re: SQL Question... Boolean Logic
>>Message-ID: <1995Apr24.120235.6502_at_newton.ccs.tuns.ca>
>>Sender: news_at_newton.ccs.tuns.ca (Usenet news)
>>Date: Mon, 24 Apr 1995 12:02:35 GMT
>>Organization: TUNS.CA
>>X-Newsreader: <WinQVT/Net v3.9>
>>Lines: 51

>>In article <ggrieffD7BDn4.84x_at_netcom.com> ggrieff_at_netcom.com (Greg Grieff)
>>writes:
>>>
>>>I have the following table:
>>>
>>>create table keywords (
>>> recid number(9) not null, /* primary key */
>>> docid number(9) not null, /* foreign key to another table */
>>> keyword varchar2(30) not null, /* keyword entry */
>>>)
>>>
>>>Sample data:
>>> recid docid keyword
>>>--------------------------------------------
>>> 1 1 apples
>>> 2 1 oranges
>>> 3 2 apples
>>> 4 2 pears
>>> 5 2 watermelon
>>>
>>>I want to return the unique values for docid that match boolean logic
>>>combinations of keyword values. For example, if I ask for all vaules for
>>>docid that have keywords, apples and oranges, I get the answer 1. I
>>>would like standard order of operations to apply to the values that I
>>>search for in keywords. And, (this is the difficult part) I would like a
>>>single SQL (non-PL/SQL) statement that does this.
>>>

 /* Edited */
>>>
>>>Any help you can give me on this would be greatly appreciated, or if
>>>perhaps you know it is not possible to do this let me know that too.
>>>
>>>- Thanks in advance
>>>- Greg
>>>
>>>-------------------------------------------------------------------------
>>>| Greg Grieff ggrieff_at_netcom.com | "Dream, Design, Develop, Debug, |
>>>| Head of Engineering | Deliver... Not necessarily in |
>>>| Micrographic Specialties Inc. | that order." |
>>>-------------------------------------------------------------------------
 

>>How about...
 

>>SELECT DISTINCT DOCID FROM KEYWORDS
>>WHERE KEYWORD IN ('apples', 'oranges');
 

>>What you will really want to do /* to automate */
>>is create an *.SQL which will create another *.SQL,
>>and run the second *.SQL file.
 

>>James Richard (JRICHARD_at_TUNS.CA)
>>"No Assembler Required!"

The problem cannot be solved in general with any single SQL query. We have a QBE SQL generator that proves that. Each boolean condition falls into one of a number of groups and each group generates completely different (optimized) SQL. Let me know if you are interested in this QBE.

>Mike Schuler - software developer
>IBX datasystems limited
>Vancouver - CANADA

Mike Schuler - software developer
IBX datasystems limited
Vancouver - CANADA Received on Tue Apr 25 1995 - 00:00:00 CEST