Re: query to find references

On Wed, 28 May 1997 08:53:54 +0200, Michiel Perdeck <perdeck.via_at_sni.de> wrote:

>I have a table A and a table B. Rows in B contain a foreign key to A.
>I want to find all instances of A to which less than a certain number,
>say N, rows in B refer. A naive query would look like this but is not
>accepted by Oracle7 ('a' is the name of the key field):
>
>SELECT * FROM A
>WHERE (SELECT COUNT(*) FROM B
> WHERE B.a = A.a) < N

Try this instead:

SELECT a.pkey, count(b.uniquecol)
FROM a, b
WHERE a.pkey = b.fkey
GROUP BY a.pkey
HAVING count(b.uniquecol) > N Received on Fri May 30 1997 - 00:00:00 CDT