Help finding single occurrences of value

Tjmertes · #1 (**permalink**) 01-24-12, 07:56

Hi
I am helping a small nonprofit straighten out their data and I am stumped on how to best do something.

I have tables

Individual table with individual.id and family.reference
Financial table with individual.ID and and family refernce

Family refernce is an indexed field with duplicates

If the family refernce code in the individual table has only one occurrence with its value I need to assign the individual table.id to the individual.id in the financial table.

Example

Individual table

Id. Family refernce
1. Fam1
2. Fam1
3. Fam2
4. Fam3
5. Fam4
6. Fam4

For Id 3 and 4 I want to set the financial table rows with a family refernce of fam2 to have an individual Id of 4 and those with a family reference of family reference of fam3 to have an individual Id of 4. I do not want any action for fam1 or fam2 because they have more than one occurrence.

Not sure if this can be done in SQL. Thanks

Sinndho · #2 (**permalink**) 01-24-12, 13:34

I'm not sure to understand what you want to achieve. For retrieving the rows having a unique family reference you can use:

Code:

SELECT Individual.Id, Individual.Family_reference
FROM Individual
WHERE Individual.Family_reference In (SELECT [Family_reference] 
                                      FROM   [Individual] 
                                      GROUP BY [Family_reference] 
                                      HAVING Count(*)=1 );

But I don't know for sure what you want to do from there: Create new rows in the other table ("Financial") or update existing rows?