Re: Nested SELECT
Robert,
If you don't care about specific types, then this might work for
you. It should return all the rows with more than one of any type.
SELECT table1.code, table1.name
FROM table1, table2
WHERE table2.code = table1.code
GROUP BY table1.code, table1.name
HAVING count(*) > 1;
If, on the otherhand, you care about the types, try:
SELECT table1.code, table1.name
FROM table1, table2
WHERE table2.code = table1.code
AND table2.type IN ("Type1", "Type2")
GROUP BY table1.code, table1.name
HAVING count(*) > 1;
I think this will work. Also, I've tried it on a couple of quick
test tables and they both returned as expected. (Of course, you have
real data so I'll let you be the judge.)
--Craig (craig@chemistry.utah.edu)
}
} 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" ) )
}