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

I don't have an Oracle database in front of me right now, but this might work. It's pretty ugly, but so are lots of other things that work.

SELECT * FROM A
WHERE exists

         (SELECT nvl(COUNT(*),0) FROM B
          WHERE  B.a = A.a
          having count(*) < N
         )

You are basically asking Oracle to do an inline view that acts like a column. I think this will work in Oracle8, but I'm not sure.

> How should I get what I want?
>

Kick and scream.

Will.

-- 
======================================================================
Will Kooiman                        Computer Systems Authority
Systems Consultant                  6380 LBJ Freeway, Suite 181
(972) 960-0180 x236                 Dallas, TX 75240
mailto:wkooiman@csac.com            http://www.csac.com