Re: SQL: partial match with set of vsalues from a subquery

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 9 Aug 2005 12:21:55 -0700
Message-ID: <1123615315.063859.199480@f14g2000cwb.googlegroups.com>

scott_at_ORA92> select * from tabA
  2 /

      CODE PATTERNHERE
---------- --------------------

         1 I am in
         2 You are in

scott_at_ORA92> select * from tabB
  2 /

      CODE NAME
---------- -----

         1 name1
         2 name2

scott_at_ORA92> select * from tabC
  2 /

PATTERN

---

I am in TOKYO
I am in NY
I am in LONDON
I am in PARIS
You are in AU
You are in NZ

6 rows selected.

scott_at_ORA92> select a.code, b.name, a.patternhere, c.pattern   2 from tabA a, tabB b, tabC c
  3 where a.code = b.code
  4 and c.pattern like '%' || a.patternhere || '%'   5 /

      CODE NAME PATTERNHERE PATTERN
---------- ----- -------------------- --------------------

         1 name1 I am in              I am in TOKYO
         1 name1 I am in              I am in NY
         1 name1 I am in              I am in LONDON
         1 name1 I am in              I am in PARIS
         2 name2 You are in           You are in AU
         2 name2 You are in           You are in NZ

6 rows selected. Received on Tue Aug 09 2005 - 14:21:55 CDT