Need help finding unique names from multiple table fields

I hate to ask

[iceghost]

Hello all,

I really hate when people post questions without trying to do their own homework first. That's why I resisted two months ago before posting, now I have given up.

I have a family tree system that contains a table that looks like this:

_________________________________________
¦ I D ¦ Person ¦ Mother ¦ Father ¦

I would like to find the total number of unique names in this table.

This table contains a couple hundread rows, any ideas?

[Robber]

Hello and welcome to WebmasterWorld,

I think something like

SELECT COUNT(DISTINCT person) FROM table_name

might do the trick

[seindal]

SELECT COUNT(*) FROM table GROUP BY field;

[iceghost]

The only problem is a name may apear in the 'father' field and not in the 'person' field or it may apear in both. I need to find a way to maybe create a string containing the contents of 'person' ,'father' and 'mother' and illiminate duplicates, then I would have a count of every unique name in the entire table. I just can't figure out how.

[Lisa]

How can a father not give birth to a person?
I would draw a ERD, and figure out what you are doing.
I hope you are using int for the father and mother, and not a string.

count(id) will count all rows.
count(father) will count all rows with father that are not NULL

Try to avoid count(*) because * is not indexed.

[edited by: Lisa at 12:19 am (utc) on Nov. 27, 2002]

[aaronc]

Maybe this?

SELECT COUNT(DISTINCT(CONCAT(person, mother, father))) FROM table;

[seindal]

Try to avoid count(*) because * is not indexed.

With which databases are count(*) a performance problem. It seems to me that any database should be able to come up with the count of records in a query real fast, and probably faster than if it had to actually look at a field to see if it is NULL.

I have always learned that count(*) is the faster!

René.