Select only certain rows from a where in query in SQL

I have a query trying to get duplicates from a table of user invites. I can get the duplicates through a “WHERE IN” clause and filter that list to be duplicates where at least one of the duplicates has a certain timestamp of when their account was created. Now I want to take that list filter out all records which have NULL in that timestamp list. That is where I am stuck.

Here is my SQL query:

select * FROM [MyDatabase].[dbo].[Invitation]
where MessageID in ( SELECT MessageID
  from [MyDatabase].[dbo].[Invitation]
  group by MessageID
  Having count(MessageID) > 1
  and count(TimeAccountCreated) > 0)
order by LastName, FirstName

I am looking to filter the subselected list to only have records of “TimeAccountCreated = NULL”. Adding an and TimeAccountCreated = NULL to my WHERE clause causes an error. I am guessing it because the WHERE clause is getting an aggregate but I am unsure. Eventually, I want to delete these rows I get from my new query that have a NULL in TimeAccountCreated. Thanks in advance.

Answer

Yeah I forgot that I needed to use IS instead of = for my TimeAccountCreated clause. That is the answer.

Advertisement

Answer