By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,824 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,824 IT Pros & Developers. It's quick & easy.

How to return total number of records form union of two tables

P: 106
Hi All

I am using the following query to select records from two tables.

Select JobTitle,JobDesc,Scraped,logoimage,JobPostID,Schoo lID,web,MemType,InstitutionName,PayScale,LEA,Contr act as ContractType,HoursAbl,StartDate,ClosingDate,asap from tblJobScrap
Union ( SELECT tblJobPost.JobTitle,tblJobPost.JobDesc,Scraped,tbl Schools.logoimage,tblJobPost.JobPostID,tblJobPost. SchoolID,tblSchools.web,cast(tblJobPost.MemType as nvarchar(255)) as MemType,tblSchools.InstitutionName,tblJobPost.PayS cale,cast(tblLEA.LEA as nvarchar(255)) as LEA,tblContract.ContractType,tblHoursAbl.HoursAbl, cast(tblJobPost.StartDate as nvarchar(255)) as StartDate, cast(tblJobPost.ClosingDate as nvarchar(255)) as ClosingDate,cast(tblJobPost.asap as nvarchar(50)) as asap FROM tblJobPost LEFT Outer JOIN tblpositions ON tblJobPost.position_id = tblpositions.position_id LEFT OUTER JOIN tblMainsubjects ON tblJobPost.SubjectID = tblMainsubjects.MSubjectID LEFT OUTER JOIN tblSchoolPhase ON tblJobPost.SchoolPhaseID = tblSchoolPhase.SchoolPhaseID LEFT OUTER JOIN tblLEA ON tblJobPost.LeaID = tblLEA.LeaID LEFT OUTER JOIN tblHoursAbl ON tblJobPost.HoursID = tblHoursAbl.HoursAblID LEFT OUTER JOIN tblContract ON tblJobPost.ContractID = tblContract.ContractID LEFT OUTER JOIN tblregion ON tblJobPost.Region_id = tblregion.RegionID RIGHT OUTER JOIN tblSchools ON tblJobPost.SchoolID = tblSchools.SchoolID where (tblJobPost.ClosingDate >= '11/28/2008 9:33:22 AM'))

This query returns the union of records from two tables. Now I just want how much records the above query returns. I try the below query

Select count(*) as numrec from( Above query Here)

Executing this I get the following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Any Help on this issue. Thanks in Advance
Nov 28 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.