Select only items that don't exist in another table

Expand Post »

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

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?

Similar Threads

[req] how to check whether given oracle table exist or not + C# in C#
Select a Set of rows from a table in MS SQL
Shopping Cart Not Working in ASP.NET
how to select the isbn number in table and get all books of isbn selected in JSP
Select all the items in a listbox right before submit. in JavaScript / DHTML / AJAX

benkyma

Reputation Points: 9

Solved Threads: 0

Light Poster

Offline

34 posts
since Sep 2009

Permalink

Oct 19th, 2009

Re: Select only items that don't exist in another table

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.

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.

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

Last edited by cgyrob; Oct 19th, 2009 at 3:04 pm.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Oct 20th, 2009

Re: Select only items that don't exist in another table

That's fantastic. Thanks a lot : )

benkyma

Reputation Points: 9

Solved Threads: 0

Light Poster

Offline

34 posts
since Sep 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SELECT statement

Next Thread in MS SQL Forum Timeline: Best way to backup a database from one server to another server in SQL Server 2005

DaniWeb Message

Cancel Changes

User Name
Password