SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to avoid inner query to compare count with a
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 05/15/2014 :  06:23:43  Show Profile  Reply with Quote
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)

Singapore
17645 Posts

Posted - 05/15/2014 :  07:54:26  Show Profile  Reply with Quote
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



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000