Re: SQL join question

Kevin Mahoney wrote:

> 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

This is not really suitable for a single SQL statement. Put together a cursor loop. And look at a syntax something like this:

A cursor loop based on the records in the keyword table where each product is evaluated individually against each keyword and then within the loop

WHERE INSTR(UPPER(product.name), keyword_record.keyword_name) > 0

Daniel Morgan Received on Thu Oct 24 2002 - 11:09:28 CDT