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

Rithban wrote:
> 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.
>

You were SO CLOSE:

 > SELECT DISTINCT a FROM A
 >   INNER JOIN B ON A.b=B.b
 >   WHERE (B.value=123 B.c=1) OR
 >         (B.value=456 B.c=2) OR
 >         (B.value=789 B.c=3);

Your hint should have been the DISTINCT. You really want more than one row back which you summarize with DISTINCT.

HTH

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost