Re: Intersect with a twist

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Wed, 20 Apr 2005 08:36:51 -0700
Message-ID: <1114011177.979570@yasure>

Dagger wrote:

> I have three queries like:
>
> SELECT id FROM a WHERE ...
> SELECT id FROM b WHERE ...
> SELECT id FROM c WHERE ...
>
> I want to join these in some way so that I get the id:s that are
> returned by all three queries.
>
> If I didn't have any more information than that, I would simply do:
>
> SELECT id FROM a WHERE ...
> INTERSECT
> SELECT id FROM b WHERE ...
> INTERSECT
> SELECT id FROM c WHERE ...
>
> But the problem is that if a subquery return NO records, that query
> should be disregarded, so for example if "a" returns 1,2,3, "b" returns
> 2,3,4 and "c" returns nothing, I want the total query to return 2,3.
>
> I have a sneaking suspicion that I'm missing the obvious answer?

One solution:

SELECT id FROM (

   SELECT id, COUNT(*) FROM (

     SELECT id FROM a
     UNION ALL
     SELECT id FROM b
     UNION ALL
     SELECT id FROM c)

   GROUP BY id
   HAVING COUNT(*) = 3);

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Wed Apr 20 2005 - 10:36:51 CDT