distinct count

[dotJoon]

myTable1
(id) name
(1) Tom
(2) Jane
(3) Chris
(4) Judy
(5) Mary
(6) Jack
(7) James
(8) Bill

myTable
(pom) kid
(1)  2
(3)  1
(3)  4
(5)  8

Let's suppose I have 2 tables like the above.

The code1 below produces the result1 below.

code1
select name
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
order by name

result1
Chris
Chris
Mary
Tom

I can count the number of records in result1 with the code below.

code2
select count(*) as count
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null

result2
4

Since Chris comes 2 times in result1, I like the code3 and its result3 below.

select distinct name
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
order by name

result3
Chris
Mary
Tom

Now I have a problem. i.e, How can I count the number of records in result3?

The following code4 is one of my trials for it. but it still says 4 instead of 3.

code4
select distinct count(*) as count
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null

result4
4

[Mikl]

I don't know if this would work in MySQL, but in SQL Server I would do this:

SELECT COUNT(DISTINCT Name) FROM ... etc.

I think that's standard ANSI syntax, so it should work with most databases.

Mike

[dotJoon]

Thank you, Mikl. It works fine in MySQL.