CODEWITHSUNDEEP
sql-servert-sql
Sam
Sam

Reputation: 30388

Using aggregate value in condition in SELECT statement

I'm creating a SELECT statement between two tables with a one-to-many relationship and I want to use the aggregate value in the condition where I want to get a list of records with more than one related records in table B.

The following SELECT statement gives me the number of related records in table2 but I only want those records in my result where there are more than one related records in table2. In other words, in the following code, I want the NumberOfRelatedRecords to be greater than 1.

How do I use that in my WHERE condition?

select a.Id, count(b.Id) as NumberOfRelatedRecords
from table1 as a
   inner join table2 as b on a.Id = b.RelatedId
group by a.Id

Upvotes: 1

Views: 35

Answers (1)

dfundako
dfundako

Reputation: 8314

You can try using HAVING

select a.Id, count(b.Id) as NumberOfRelatedRecords
from table1 as a
   inner join table2 as b on a.Id = b.RelatedId
group by a.Id
HAVING Count(b.ID) > 1

Upvotes: 1

Related Questions