how to avoid inner query to compare count with a

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to avoid inner query to compare count with a

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2014-05-15 : 06:23:43

how to avoid inner query to compare count of a inner query record with a column of main query. want's to do it via some form of joining

SELECT ce.Id]
FROM [dbo].[coordinator_event] ce
where IsActive = 1
and Dateadd(day, 3, convert(DATE, Getdate())) = CONVERT(DATE, RegistrationClosingDate)
and (select Count(*)
from event_attendee_registration ear
where ce.CoordinatorId = ear.CoordinatorId
and ear.EventId = ce.EventId) < ce.NumberOfParticipantAllowed

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-15 : 07:54:26

[code]SELECT ce.[Id]
FROM [dbo].[coordinator_event] ce
INNER JOIN
(
SELECT CoordinatorId, EventId, cnt = COUNT(*)
FROM event_attendee_registration
GROUP BY CoordinatorId, EventId
) ear ON ce.CoordinatorId = ear.CoordinatorId
AND ce.EventId = ear.EventId
WHERE ear.cnt <= ce.NumberOfParticipantAllowed
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources