Re: SQL join question

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3DB81B23.3A31A22_at_exesolutions.com>...
> 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

or you can build a dynamic SQL which looks like,  ... where upper(p.name) like '%GREEN%' and upper(p.name) like '%SHIRT%'... (You'll need to loop through each row in the keyword table to do this) and open your ref cursor using this query.

If you are only returning one column, have you considered returning your result set in an array instead of a ref cursor?

>
> Daniel Morgan
Received on Thu Oct 24 2002 - 18:06:01 CDT