Re: Intersect with a twist

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?

I don't think there is an obvious answer. The only way I can think to do it would be to join all three tables then union that with joins of two of the three where the third one doesn't exist.

SELECT a.id FROM a, b, c WHERE ...
AND a.id = b.id AND b.id = c.id
UNION
SELECT a.id FROM a, b WHERE ...
AND a.id = b.id
AND NOT EXISTS (SELECT 1 FROM c WHERE ...) UNION
SELECT b.id FROM b, c WHERE ...
AND b.id = c.id
AND NOT EXISTS (SELECT 1 FROM a WHERE ...) UNION
SELECT a.id FROM a, c WHERE ...
AND a.id = c.id
AND NOT EXISTS (SELECT 1 FROM b WHERE ...); Received on Wed Apr 20 2005 - 07:22:07 CDT