SQL join question

From: Kevin Mahoney <kgmahoney_at_yahoo.com>
Date: 24 Oct 2002 08:51:08 -0700
Message-ID: <f775e61e.0210240751.25400370@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 Thu Oct 24 2002 - 10:51:08 CDT