Problem in where clause and aliases in MySQL

What's wrong with this mysql query :

select * 
    from tpa as t1 
    where ( select count(*) 
                from tpa as t2 
                where t1.id = t2.id )

error :

Error Code: 1054. Unknown column 't开发者_C百科1.id' in 'field list'

I think, as Cfreak pointed out in comment, that the alias is not visible in the subquery.

I think also that you forgot to specify some condition for your count(*) result to be equal to some-number (or other condition):

select * from tpa as t1 
where
(
select count(*) from
(
select * from tpa
)
as t2
where t1.id = t2.c_id 
) = 1

Change "= 1" with any numery condition you like, or this would be a silly way to rewrite this much simpler query:

select distinct * from tpa

:-)

select id, count(*) from tpa as t1
group by id

/*If you need a certain count.. add-*/
having count(*) > 2

You should not need to use a subquery in either case.