problem with join and count, please help!

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 2000 Forums

SQL Server Development (2000)

problem with join and count, please help!

Author

Topic

smartsl
Starting Member

6 Posts

Posted - 2007-06-22 : 19:51:41

hi, i have a simple question, i think.

I have 3 tables, tblMembers, tblSignups, and tblSignupResponses. I have put together a "sign-up" for a web application, and am having trouble with counting. The user (from tblMembers) will create a signup sheet in tblSignups, in that table they will tell info about the signup (start date, end date, and the MAX NUMBER OF PEOPLE WHO CAN SIGNUP.

I need to:
SELECT tblMembers.*, tblSignups.*, tblSignupResponses.*

WHERE tblMembers.IsActive = 1, tblSignups.IsActive = 1, tblSignupResponses.IsActive = 1

then finally...

COUNT all of the records from the tblSignupResponses that has the same SignupID as a join. When I run the code I have, it brings back all of the records from the tblSignupResponses as the COUNT for each signup sheet. What is the best way to code this?

Here is my exact code:

SELECT tblSignups.SignupID, tblSignups.NationalID, tblSignups.LocalID, tblSignups.AreaID, tblSignups.DateCreated, tblSignups.IsActive, tblSignups.DateStart,
tblSignups.DateEnd, tblSignups.AllowComments, tblSignups.Description, tblSignups.Title, tblSignups.CreatedBy, tblMembers.FirstName,
tblMembers.LastName,
(SELECT COUNT(ResponseID) AS Expr1
FROM tblSignupResponses AS tblSignupResponses_1
WHERE (IsActive = '1') AND (NationalID = @NationalID2) AND (tblSignupResponses = @LocalID)) AS Expr2
FROM tblSignups LEFT OUTER JOIN
tblMembers ON tblSignups.CreatedBy = tblMembers.UserID LEFT OUTER JOIN
tblSignupResponses AS tblSignupResponses ON tblSignups.SignupID = tblSignupResponses.SignupID
WHERE (tblSignups.NationalID = @NationalID) AND (tblSignups.LocalID = @LocalID) AND (tblSignups.AreaID = @AreaID) AND (tblSignups.IsActive = 1) AND
(tblSignups.DateStart < { fn NOW() }) AND (tblSignups.DateEnd > { fn NOW() })
ORDER BY tblSignups.DateCreated DESC, tblSignups.DateEnd DESC

smartsl
Starting Member

6 Posts

Posted - 2007-06-23 : 02:05:40

hi, if my above post was unclear i have tried to simplify what i need to do:

using my three tables (tblSignups, tblSignupResponses, and tblMembers) I need to create a double left join from tblSignups, then COUNT the number of records in tblSignupResponses that have the same SignupID.

Something like:
SELECT tblMembers.*, tblSignups.*, tblSignupResponses.*
FROM tblMembers, LEFT JOIN tblSignups ON tblSignups.CreatedBY = tblMembers.UserID LEFT JOIN tblSignupResponses ON tblSignupResponses.SignupID = tblSignups.SignupID
WHERE (tblMembers.IsActive = 1) AND (SLECT COUNT(*) FROM tblSignupResponses WHERE tblSignupResponses.IsActive = 1)

i hope someone can help me this is driving me nuts and really slowing me down!

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-25 : 01:30:48

Post some sample data and required output, so that everyone will understand easily..

--------------------------------------------------
S.Ahamed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources