kdc_landrover
29 Oct 2009, 10:03 AM
Hi, I’m working on a website for a recruitment company, and am having some problems with paging results using MSSQL.
After doing some research I understand that you need to use a nested TOP query (something like this: http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/), but have been unable to get this to work with our large query below which pulls data from multiple tables. Essentially all this query is pulling is the following: job title, salary, location, job type, date posted, description, consultants email address. This covers 5 different tables as a result of the local front end software package, which was built in conjunction with these tables.
SELECT
EmploymentTypes.Description AS JobType, Jobs.JobTitle, Jobs.JobId, Jobs.JobRefNo, Jobs.LocationId, Jobs.PublishedJobDescription, Jobs.UpdatedOn, Jobs.Salary, Locations.Description, Users.EmailAddress
FROM
Jobs, Locations, Users, JobConsultants, EmploymentTypes
WHERE
Jobs.Published = 'Y'
AND (EmploymentTypes.EmploymentTypeId = Jobs.EmploymentTypeId)
AND (JobConsultants.UserId = Users.UserId)
AND (Jobs.JobId = JobConsultants.JobId)
AND (Locations.LocationId = Jobs.LocationId)
AND (JobConsultants.UserRelationshipId = '9')
ORDER BY ".$sql_ordertemp."
Can anyone show me how to modify the above query to incorporate the nested TOP statement? I assume that the TOP values will be substituted with PHP variables?
Any help would be greatly appreciated! Thanks…
After doing some research I understand that you need to use a nested TOP query (something like this: http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/), but have been unable to get this to work with our large query below which pulls data from multiple tables. Essentially all this query is pulling is the following: job title, salary, location, job type, date posted, description, consultants email address. This covers 5 different tables as a result of the local front end software package, which was built in conjunction with these tables.
SELECT
EmploymentTypes.Description AS JobType, Jobs.JobTitle, Jobs.JobId, Jobs.JobRefNo, Jobs.LocationId, Jobs.PublishedJobDescription, Jobs.UpdatedOn, Jobs.Salary, Locations.Description, Users.EmailAddress
FROM
Jobs, Locations, Users, JobConsultants, EmploymentTypes
WHERE
Jobs.Published = 'Y'
AND (EmploymentTypes.EmploymentTypeId = Jobs.EmploymentTypeId)
AND (JobConsultants.UserId = Users.UserId)
AND (Jobs.JobId = JobConsultants.JobId)
AND (Locations.LocationId = Jobs.LocationId)
AND (JobConsultants.UserRelationshipId = '9')
ORDER BY ".$sql_ordertemp."
Can anyone show me how to modify the above query to incorporate the nested TOP statement? I assume that the TOP values will be substituted with PHP variables?
Any help would be greatly appreciated! Thanks…