Re: SQL join question

From: M Hashim <m.a.n.hashim_at_sympatico.ca>
Date: Thu, 24 Oct 2002 23:14:13 -0700
Message-ID: <OC2u9.35147$U17.1312711@news20.bellglobal.com>

"all Products that have ALL the keywords in the keyword table"

SELECT Name
FROM ProductTable a
WHERE Name IN(SELECT a.Name

                        FROM KeywordTable b
                        WHERE UPPER(a.Name) LIKE '%'||b.Name||'%'
                        HAVING COUNT(*)=(SELECT COUNT(*) FROM

KeywordTable ))
;

"Kevin Mahoney" <kgmahoney_at_yahoo.com> wrote in message news:f775e61e.0210240751.25400370_at_posting.google.com...
> I am using Oracle 9i Release 2 and I am trying to pass a ref cursor
> with results back to a Java program. The tables look like the
> following:
>
> Product Table
>
> Product_id Name
> --------------------------------------------------
> 1 Large Green Shirt
> 2 Green Socks
> 3 Black Pants
>
> Keyword Table
>
> Name
> --------
> GREEN
> SHIRT
>
> I am trying to have one SQL statement that returns all Products that
> have ALL the keywords in the keyword table exists somewhere in their
> name
>
> The query like the following
>
> SELECT DISTINCT p.product_id
> FROM product p, keyword k
> WHERE upper(p.name) like '%' || k.keyword || '%'
>
> returns records 1 and 2 above, but I only want 1 since that is the
> only record with both green AND shirt in it. Is there a way to get the
> results I want in one SQL statement or do I have to do it with dynamic
> SQL instead? Thanks again for any help you can provide.
>
> Kevin Mahoney
Received on Fri Oct 25 2002 - 01:14:13 CDT