Nested SELECT
What we want is to select from one table where there are two or more
of a specific type records in another table ( they are a one-to-many
relationship). For example:
Table1 ( unique code for every record )
--------------------------
code char(10)
name char(50)
status char(10)
Table2 ( links to Table1 via code, unique (code, type) combination )
--------------------------
code char(10)
type char(20)
Now, what we want, for one example, is to select all from table1 where,
let's say, there is a type of "Type1" and a type of "Type2" in table2.
Here is our select statement, which is a double nested select statement,
and works. We will need to go 5 deep at some instances. What we want to
know is if this can be optimized any more or attacked at a different
angle:
SELECT code, name FROM Table1
WHERE code IN
( SELECT code FROM Table2 WHERE type = "Type1" AND code IN
( SELECT code FROM Table2 WHERE type = "Type2" ) )
Responses directly to me are appreciated, but not necessary.
Thanks in advance,
Robert Minter Data Systems Support \\\\\\_///
Senior Software Engineer A Client Technologies Company ( _ _ )
E-Mail: rob@dssmktg.com Tel: 714.771.0454 (| ^ |)
#include <disclaimer.h> Fax: 714.771.3028 \\`-'/
De Colores - Emmaus OC-13 SURF'S UP \\_/