Complex Join OK on first page but not on second

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

Transact-SQL (2000)

Complex Join OK on first page but not on second

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-21 : 17:32:53

The following query returns the correct results when I retreive the first set of records but when I move to the next page it just fills up the recordset with a single record. When I advance to the next page it fills it with the next record instead of the next group of 30.

Am I doing something wrong here with the join on using this join "INNER JOIN UF_rt_friends ON UF_rt_friends.to_id=UF_rt_images.user_id)" and this WHERE "AND (UF_rt_Friends.From_ID = 82)" Everything worked ok until I added these.

The friends table contains 2 columns, the "From" column which is me (82) and the "To" column which are the "user_id" s of my friends. The idea being to only return records from the images table that are my friends (have a user_id in my friends table).

I am using a stored procedure to step through the records where it skips the previous rows and gets the next set. I have included that stored procedure at the end of this message. For Readability the query below is re-constituted inserting the SELECT FROM WHERE and ORDER BY statements, normally the stored procedure would add them.

Any help would be apprciated.

Phil

Select UF_rt_images.image_id AS image_id,
UF_rt_images.user_id AS user_id,
UF_rt_images.camera_id AS camera_id,
UF_rt_images.image_title AS image_title,
UF_rt_images.editors_choice AS editors_choice, UF_rt_images.staff_choice AS staff_choice, UF_rt_images.featured_comments AS featured_comment, UF_rt_images.image_rateable AS rateable,
UF_rt_images.image_adult AS adult,
UF_rt_images.image_view_count AS view_count, UF_rt_images.comment_count AS comment_count, UF_rt_images.rating_count AS rating_count, UF_rt_images.favorite_count AS favorite_count, UF_rt_images.rating_total AS rating_total,
UF_rt_cameras.camera_model AS camera_model,
UF2_rt_users.user_donor AS donor,
UF2_rt_users.user_Gender AS user_gender, UF2_rt_users.user_Allowed_Top_Photo AS user_Allowed_Top_Photo, UF_rt_cameras.manufacturer_id AS camera_mfg_id, UF2_rt_users.user_last_name AS Last_Name, UF2_rt_users.user_first_name AS First_Name, UF_rt_manufacturers.manufacturer_name AS Camera_Mfg,
UF_rt_images.image_ID
FROM ((((UF_rt_images INNER JOIN UF_rt_friends ON UF_rt_friends.to_id=UF_rt_images.user_id)

INNER JOIN UF2_rt_users ON UF2_rt_users.user_id=UF_rt_images.user_id)
LEFT JOIN UF_rt_cameras ON UF_rt_cameras.camera_id=UF_rt_images.camera_id)
LEFT JOIN UF_rt_manufacturers ON UF_rt_manufacturers.manufacturer_id=UF_rt_cameras.manufacturer_id)

WHERE ((UF_rt_Images.Image_Active = 1) AND (UF_rt_Friends.From_ID = 82))

ORDER BY UF_rt_images.Image_ID DESC

Stored Procedure

REATE PROCEDURE UF2_sp_Record_Paging (
@strFields VARCHAR(4000) ,
@strPK VARCHAR(100),
@strTables VARCHAR(4000),
@strFilter VARCHAR(8000) = NULL,
@strSort VARCHAR(8000) = NULL,
@strGroup VARCHAR(8000) = NULL,
@intPageNo INT = 1,
@intPageSize INT = NULL,
@blnGetRecordCount BIT = 0,
@strCountTables VARCHAR(500)
)

/*
PURPOSE: executes a select statement as defined by the parameters and returns a particular page of data (or all rows) efficiently

*/

AS
DECLARE @blnBringAllRecords BIT
DECLARE @strPageNo VARCHAR(50)
DECLARE @strPageSize VARCHAR(50)
DECLARE @strSkippedRows VARCHAR(50)

DECLARE @strFilterCriteria VARCHAR(8000)
DECLARE @strSimpleFilter VARCHAR(8000)
DECLARE @strSortCriteria VARCHAR(8000)
DECLARE @strGroupCriteria VARCHAR(8000)

DECLARE @intRecordcount INT
DECLARE @intPagecount INT

--******** NORMALIZE THE PAGING CRITERIA
--if no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will set a flag that will help with that (blnBringAllRecords)

IF @intPageNo < 1
SET @intPageNo = 1

SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)

IF @intPageSize IS NULL OR @intPageSize < 1 -- BRING ALL RECORDS, DON'T DO PAGING
SET @blnBringAllRecords = 1
ELSE
BEGIN
SET @blnBringAllRecords = 0
SET @strPageSize = CONVERT(VARCHAR(50), @intPageSize)
SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
SET @strSkippedRows = CONVERT(VARCHAR(50), @intPageSize * (@intPageNo - 1))
END

--******** NORMALIZE THE FILTER AND SORTING CRITERIA
--if they are empty, we will avoid filtering and sorting, respectively, executing more efficient queries

IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilterCriteria = ''
END

IF @strSort IS NOT NULL AND @strSort != ''
SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
SET @strSortCriteria = ''

IF @strGroup IS NOT NULL AND @strGroup != ''
SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
ELSE
SET @strGroupCriteria = ''

--************************** NOW START DOING THE REAL WORK
--!NOTE: for potentially improved performance, use sp_executesql instead of EXEC

IF @blnBringAllRecords = 1 --ignore paging and run a simple select
BEGIN

EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
)

END-- WE HAD TO BRING ALL RECORDS
ELSE --BRING ONLY A PARTICULAR PAGE
BEGIN
IF @intPageNo = 1 --in this case we can execute a more efficient query, with no subqueries
EXEC (
'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
ELSE --execute a structure of subqueries that brings the correct page
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + ' IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTables +
' WHERE ' + @strPK + ' NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)

END --WE HAD TO BRING A PARTICULAR PAGE

--IF WE NEED TO RETURN THE RECORDCOUNT
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strCountTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strCountTables + @strFilterCriteria + @strGroupCriteria
)
GO

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-21 : 17:39:22

I wouldn't use a paging procedure like this. Of course, I am biased: Check out mine at http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx . Be sure to read the entire article carefully, plus the link to the article that describes the technique in detail (and presents the original non-dynamic method which is the preferred method if your sort columns are constants).

Any paging procedure that relies on a single-column primary key and that works that hard to generate a SQL statement for you is way too complicated and way too much trouble. You don't want to rely on a stored proc to write your SELECT and do joins, groups, conditions, etc for you -- you want to pass in the entire SQL statement all at once and let the proc handle only the paging.

- Jeff

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-21 : 23:15:11

Jeff,

I tried using your proc but cold not get it to work. I wrote you an email but perhaps you didn't get it.

One of the problems is that the order is not fixed. there are about 10 different options as far as order by selectable by a drop down on the site page.

This proc seems to work well on everything but this, the table has around a 500,000 records, growing at about 1000 records per day and ramping up. The query times seem to be ok.

I will give yours another try and see if I can make it work.

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-21 : 23:19:21

Jeff,

One other thing, it was not clear in your proc how it determines the WHERE portion of the proc. Is there a parameter missing or am I not seeing how it is derived from the select parameter. Do I include the word WHERE in the select statement?

Thanks,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-22 : 05:44:35

JEFF

I tried your proc again, this is the string that I sent to it:

exec returnpage 'Select UF_rt_images.image_id AS image_id, UF_rt_images.user_id AS user_id, UF_rt_images.camera_id AS camera_id, UF_rt_images.image_title AS image_title, UF_rt_images.editors_choice AS editors_choice, UF_rt_images.staff_choice AS staff_choice, UF_rt_images.featured_comments AS featured_comment, UF_rt_images.image_rateable AS rateable, UF_rt_images.image_adult AS adult, UF_rt_images.image_view_count AS view_count, UF_rt_images.comment_count AS comment_count, UF_rt_images.rating_count AS rating_count, UF_rt_images.favorite_count AS favorite_count, UF_rt_images.rating_total AS rating_total, UF_rt_cameras.camera_model AS camera_model, UF2_rt_users.user_donor AS donor, UF2_rt_users.user_Gender AS user_gender, UF2_rt_users.user_Allowed_Top_Photo AS user_Allowed_Top_Photo, UF_rt_cameras.manufacturer_id AS camera_mfg_id, UF2_rt_users.user_last_name AS Last_Name, UF2_rt_users.user_first_name AS First_Name, UF_rt_manufacturers.manufacturer_name AS Camera_Mfg FROM ((((UF_rt_images INNER JOIN UF_rt_friends ON UF_rt_friends.to_id=UF_rt_images.user_id) LEFT JOIN UF2_rt_users ON UF2_rt_users.user_id=UF_rt_images.user_id) LEFT JOIN UF_rt_cameras ON UF_rt_cameras.camera_id=UF_rt_images.camera_id) LEFT JOIN UF_rt_manufacturers ON UF_rt_manufacturers.manufacturer_id=UF_rt_cameras.manufacturer_id) WHERE ((UF_rt_Images.Image_Active = 1) AND (UF_rt_Friends.From_ID = 82))','UF_rt_images.Image_ID DESC', 1, 30

I got this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.

If I run the query itself in the QA it works fine and returns the correct records. So that part is sound. Comparing what I send the stored procedure to your samples it looks OK, it matches your format.

Any input on this?

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-22 : 17:20:21

you just indicate the sort columns as they are returned by your SELECT statement, not where they live in your original table.
so, for your order by, you'd have:

'Image_ID DESC'

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources