Re: Nested SELECT
On 4 Oct 1995 12:53:04 -0400, you wrote:
>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.
Try
SELECT t1.code,
t1.name,
count(*)
FROM table1 t1,table t2
WHERE t2.code = t1.code
AND t2.type IN ("TYPE1", "TYPE2") -- and so on...
GROUP BY 1, 2
HAVING count(*) > 1 -- Number of t2.type's
ORDER BY 1, 2
Regards
<><><><><><><><><><><><><><><><><><><><><>
<> Cliff Warrington <>
<> KTL Partnership <>
<> Tel : +44 (0)161 763 1286 <>
<> Fax : +44 (0)161 763 1180 <>
<> Email : cliff@ktl.u-net.com <>
<><><><><><><><><><><><><><><><><><><><><>