Re: SQL Question... Boolean Logic

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.