Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: query to find references
Michiel Perdeck wrote:
>
> Hi you SQL guru's out there, please help me with the following query:
>
> 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
>
> Why doesn't this simple query work?
> How should I get what I want?
>
> (snip)
The subquery must be on the right of the comparison: where N > (select...)
Chrysalis Received on Wed May 28 1997 - 00:00:00 CDT