Basic(?) SQL question -- set operations on >1 result set ???

I'm a programmer, not an SQL guru, so forgive my ignorance. I've hit a roadblock with a section of code that generates SQL on the fly. I'm looking for a general solution, but believe that I've reduced the problem down to the following simple example.

The short description is that I'm trying to perform set operations (i.e. intersection, union) on N separate result sets using a single query.

For a step-by step example:

Take two tables, A and B:

A has two columns:
--

a integer (unique)
b integer (unique)

B has three columns:
--

b integer
value integer
c integer

The "b" field forms a 1:* relationship between tables A and B.

Assume further that the two tables have rows such as:

A.a A.b
--- ---

100 1

B.b B.value B.c
--- ------- ---

1   123     1
1   456     2
1   789     3

I can perform three (for example) separate queries thus, and return the same value (100) from A.a:

SELECT DISTINCT a FROM A

   INNER JOIN B ON A.b=B.b
   WHERE B.value=123 B.c=1;

SELECT DISTINCT a FROM A

   INNER JOIN B ON A.b=B.b
   WHERE B.value=456 B.c=2;

SELECT DISTINCT a FROM A

   INNER JOIN B ON A.b=B.b
   WHERE B.value=789 B.c=3;

The problem I'm having is figuring out how to perform an intersection on the three result sets with a single query. My initial attempt looked logical at first:

SELECT DISTINCT a FROM A

   INNER JOIN B ON A.b=B.b

   WHERE (B.value=123 B.c=1) AND
         (B.value=456 B.c=2) AND
         (B.value=789 B.c=3);

But it returns nothing -- it finally dawned on me that the WHERE clause was restricting the results to rows in B that met all three criteria, which of course is impossible.

I've struggled with this for most of the day, and finally admitted to myself that I haven't a clue.

Is it possible to merge separate result sets into a single query, or am I going to have to jump through hoops?

Thanks for your patience.

r. Received on Thu Jun 05 2003 - 17:29:21 CDT