Finding rows by exact matches (outside joins in from statement)

From: <bjorn_at_strakt.com>
Date: 10 Feb 2005 08:25:52 -0800
Message-ID: <1108052752.645259.307960@o13g2000cwo.googlegroups.com>

We're currently adapting an application from Postgres and MS SQL Server to include support for Oracle. However, we seem to have hit a snag when it comes to one of our queries. Basically, we want to find the rows that are matched in a joined table by _exactly_ a certain set of items.

Eg: Let's say we want to find all rows in A that are matched in B by exactly two rows, with values 123 and 456. Here's our present SQL:

CREATE TYPE num_array AS TABLE OF NUMBER;

SELECT id FROM A
WHERE
  NOT EXISTS(
    SELECT NULL FROM (

      SELECT value FROM B
      WHERE
        B.a_id = A.id) T
    FULL OUTER JOIN (
      SELECT column_value AS v FROM TABLE(num_array(123, 456))) V ON

T.value = V.v

    WHERE
      TUPLE.value is NULL OR V.v is NULL))

This works swimmingly for Postgres and MS SQL, but in Oracle, the subquery in the FROM statement on lines 5-7 is apparently not allowed to join to tables outside the FROM. We get ORA-00904: "A"."id": invalid identifier

I have a rather messy solution, which works as long as there aren't any duplicate rows. I'd rather avoid using it, though. (It involves checking that the number of matching rows in B are the same as in the list of values, and that there aren't any values other than those specified)

Thoughts? Any help would be much appreciated.

// Bjorn Sandberg
AB STRAKT
http://www.strakt.com/ Received on Thu Feb 10 2005 - 10:25:52 CST