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

On 2005-02-10, bjorn_at_strakt.com <bjorn_at_strakt.com> wrote:
> 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))

Assuming a.id is a primary key, how about this:

select a.id
  from a, b
 where a.id = b.a_id
   and b.value in (123,456)
 group by a.id having count(*) = 2

Now mind you, this assumes that there could be two rows in b with the same value -- perhaps two rows with 123 or two rows with 456. If it has to to have exactly one of each then the query would look slightly different.

HTH. Ingo Received on Fri Feb 11 2005 - 15:56:17 CST