Re: SQL Question... Boolean Logic

In article <3nrcqt$cqj_at_sword.eng.pyramid.com> graeme_at_pyramid.com (Graeme Sargent) writes:
>JamesRichard wrote:
>: 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');
>
>Unless I'm going bananas, that gives apples OR oranges, not apples AND
>oranges (as requested).
>
>I think it should be:
>
> SELECT DOCID FROM KEYWORDS
> WHERE KEYWORD IN ('apples', 'oranges')
> GROUP BY docid
> HAVING COUNT(*) = 2;
>
>--
>graeme
>--
>Disclaimer: The author's opinions are his own, and not necessarily
> those of Pyramid Technology Ltd. or Pyramid Technology Inc.
>---------------------------------------------------------------------------
> -m------- Graeme Sargent Voice: +44 (0)252 373035
> ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135
> -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who???
>-------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme_at_pyra.co.uk
>---------------------------------------------------------------------------
> We have the technology. The tricky bit is learning how to use it.

I think your concern is the DISTINCT clause. The way I have it,
with the DISTINCT clause, will return 1, 2, without the DISTINCT clause, will return 1, 1, 2. The original intention was "Unique" DocID.

Your solution is incorrect because of the HAVING COUNT (*) = 2 clause. It will only return a DocID if it occurs ONLY TWICE. Your script will return only 1 and not 2.

I stand by my solution!!! /* :-) */

James Richard (JRICHARD_at_TUNS.CA)
"No Assembler Required!" Received on Mon May 01 1995 - 00:00:00 CEST