MS SQL

Select only items that don't exist in another table

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Sep 2009

Posts: 30

Reputation: benkyma is an unknown quantity at this point

Solved Threads: 0

benkyma

Offline

Light Poster

Select only items that don't exist in another table

Oct 19th, 2009

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT title, Count(*) AS Cnt FROM poss_titles WHERE Cnt > 1 GROUP BY Title ORDER BY Cnt DESC
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?

•

Join Date: Sep 2008

Posts: 123

Reputation: cgyrob is on a distinguished road

Solved Threads: 18

cgyrob

Offline

Junior Poster

Oct 19th, 2009

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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT title, count(*) As Cnt 
FROM poss_titles 
GROUP BY title
HAVING count(*) > 1 
ORDER BY count(*) desc
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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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