how to find records in db that only occur once 2

[maggielady]

I have a database that has a family id that keeps families together. I need to extract into another table all the family id's that occur in the database only once. This will tell me how many families I have that don't have an individual record to go with them. Thanks in advance!!

 

[EzLogic]

SELE COUNT(FamId) as Dupes, FamId;
FROM YourTable;
HAVING COUNT(FamId) > 1 ;
ORDER BY FamID ;
GROUP BY FamId Into Cursor Dupes

Sele * from YourTable where FamId not in (Sele FamId from Dupes) into Cursor NotDupes

Cursor NotDupes should have the non duplicates in them.




Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[maggielady]

Thanks, worked great!!

 

[bborissov]

You can do this in only one select

SELE COUNT(*) as Cnt, FamId;
FROM YourTable;
HAVING COUNT(*) == 1 ;
ORDER BY FamID ;
GROUP BY FamId Into Cursor NonDupes

Borislav Borissov

 

[CylonLove4Life]

I know you allready recived a answer here but I think a much simpler way to do this is:

SELECT DISTINCT FamId INTO CURSOR curfamid READWRITE

That way you select only the family id's that occur in the database once, and it is in a cursor. The readwrite at the end is optional, it just makes the cursor editable if need be.

 

[JimWWinter]

The DISTINCT approach won't work for this because it picks up an instance of every FamilyID no matter how many times they exist in the data. I'd use Borislav's approach.

Regards,
Jim

 

[Stella740pl]

And you don't have to actually select the COUNT(*) AS Cnt field, too.

It will work the way you need it like this:

SELECT FamId ;
   FROM YourTable;
   ORDER BY FamID ;
   GROUP BY FamId ;
   HAVING COUNT(*)=1 ;
   INTO CURSOR NonDupes

 

[CylonLove4Life]

OK sorry, guess I misunderstood the question, I thought maggielady was wanting to get all of the "distinct" family numbers, but maggielady was really wanting the family numbers that occurred only once.
My Mistake.