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…

If you want to query from different tables, you must define the relationship within the tables. To do that you can use INNER JOIN, not WHERE condition.