[B]myTable1
(id) name[/B]
(1) Tom
(2) Jane
(3) Chris
(4) Judy
(5) Mary
(6) Jack
(7) James
(8) Bill
[B]myTable
(pom) kid[/B]
(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.
[B]code1[/B]
select name
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
order by name
[B]result1[/B]
Chris
Chris
Mary
Tom
I can count the number of records in result1 with the code below.
[B]code2[/B]
[COLOR="#FF0000"]select count(*) as count[/COLOR]
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
[B]result2[/B]
4
Since Chris comes 2 times in result1, I like the code3 and its result3 below.
select [COLOR="#FF0000"]distinct[/COLOR] name
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
order by name
[B]result3[/B]
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.
[B]code4[/B]
select [COLOR="#FF0000"]distinct[/COLOR] count(*) as count
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
[B]result4[/B]
4