Select only items that don't exist in another table

[benkyma]

I want to select from a table only when a field repeats more than once. For this I have:

Select title, Count(*) As Cnt From poss_titles where Cnt > 1 Group By Title Order By Cnt desc

But it says invalid column name cnt. How should I refer to this dynamic field?
Also, I only really want titles which dont exist in another table. I believe a left join is required for this but I'm not too sure how to do it.
Can anyone offer me some pointers?

[cgyrob]

You can't use the alias in the boolean test plus you should use a having clause to test the count(*) and not the where clause.

Select title, count(*) As Cnt 
From poss_titles 
Group By title
Having count(*) > 1 
Order By count(*) desc

as for only showing fields that don't exist in another table you can use exists condition.

Select title, count(*) As Cnt 
From poss_titles pt
Where not exists (select title from other_table ot where ot.title = pt.title)
Group By title
Having count(*) > 1 
Order By count(*) desc

[benkyma]

That's fantastic. Thanks a lot : )