November 22, 2006 at 10:35 pm
I have a searching SP that uses the row_number() function to help out with paging. But for some weird reason Im getting duplicate results when I run the query. But if I take out the row_number() funciton I get normal results. Here is what I have got, please help me with getting this solved, im tearing my hair out!
WITH SearchTable AS
(
select distinct
st.id,
st.title,
st.sub_title,
st.synopsis,
st.short_code,
ROW_NUMBER() OVER (ORDER BY st.title asc) AS RowNumber
from stock as st left join
Keywords on Keywords.stock_id = st.id left join
OnlinePreviews opLow ON opLow.stock_id = st.id and opLow.[type] = 1 left join
OnlinePreviews opHigh ON opHigh.stock_id = st.id and opHigh.[type] = 2 left join
TeachersNotes tn ON tn.stock_id = st.id inner join
Stock_Subjects ss ON ss.stock_id = st.id inner join
Subjects sub ON sub.id = ss.subject_id
)
select
id,
title,
sub_title,
synopsis,
short_code,
RowNumber
from SearchTable st
Where
RowNumber between ((@page - 1) * @results) AND (@page * @results)
Order by short_code
So thats the SQL, but keep in mind the entire SQL works fine if we take all all references to the row_number() function. If I leave the row_function() in then I can work out that it is my inner joins thats the problem, if I slowly remove the inner joins and keep in the row_number(), I figured out that the joins on the Stock_Subjects table is the problem.
Why would this give different results when I use the row_number() funciton?
Thanks heaps to who ever solves this, I just can't figure it out!
November 23, 2006 at 7:10 am
The problem I guess is that the table Stock_Subjects has many to 1 relation to the table Stock.
Since you are not using any columns from that table, you can take the join out of the FROM clause and use a WHERE EXISTS(SELECT 1 FROM Stock_Subjects ss WHERE ss.stock_id = st.id)
E.g.:
WITH SearchTable AS
(
select distinct
st.id,
st.title,
st.sub_title,
st.synopsis,
st.short_code,
ROW_NUMBER() OVER (ORDER BY st.title asc) AS RowNumber
from stock as st left join
Keywords on Keywords.stock_id = st.id left join
OnlinePreviews opLow ON opLow.stock_id = st.id and opLow.[type] = 1 left join
OnlinePreviews opHigh ON opHigh.stock_id = st.id and opHigh.[type] = 2 left join
TeachersNotes tn ON tn.stock_id = st.id inner join
Subjects sub ON sub.id = ss.subject_id
WHERE EXISTS(SELECT 1 FROM Stock_Subjects ss WHERE ss.stock_id = st.id)
)
select
id,
title,
sub_title,
synopsis,
short_code,
RowNumber
from SearchTable st
Where
RowNumber between ((@page - 1) * @results) AND (@page * @results)
Order by short_code
November 23, 2006 at 2:43 pm
Will that Exists function slow down the query at all?
I mean, in terms of the performance of that function in comparison to a join.
Also it seems that when I use that Exists() function I can no longer join to the subjects table which is joined to the stock_subjects. Is there any way I can join to a table inside the Exists() function?
November 23, 2006 at 3:17 pm
Well maybe I can go about doing this another way, the issue here is that when a user passes a comman seperated list of subject id's to my SP, i need to return all stock items that relate to any of the id's in that comma seperated list.
This is what I have so far (shortened)
select distinct
st.id,
st.title,
st.sub_title,
st.synopsis,
ROW_NUMBER() OVER (ORDER BY st.title asc) AS RowNumber
from stock as st left join
Keywords on Keywords.stock_id = st.id left join
Stock_Subjects ss ON ss.stock_id = st.id inner join
Subjects sub ON sub.id = ss.subject_id
Where
--Subject, if specified, list onlu stock items for given subject id
(
@subject_list = '0'
OR
(dbo.IsInCommaList(@subject_list, sub.id) = 1 AND sub.parent_subject_id is null)
)
AND
--Sub-subject, if specified, list onlu stock items for given sub_subject id
(
@sub_subject_id = 0
OR
(sub.id = @sub_subject_id AND sub.parent_subject_id is not null)
)
Note: dbo.IsInCommaList just checks to see if the given subject id is in the comma seperated list, this works fine, but I cant use it because of the issue with the joins.
Can anyone think of a better way of doing this?
November 25, 2006 at 9:55 am
>>Can anyone think of a better way of ...
>>dbo.IsInCommaList(@subject_list, sub.id) = 1
>>works fine, but I cant use it because of the issue with the joins.
Not sure if this is a "better" way but I just had a similar requirement (item in comma-separated list) and I *was* able to solve it with a join. It looked something like this:
set @subject_list = ',' + @subject_list + ',' -- make sure starting and ending delimiters are in place
-- then set your join to something like this (not sure -- I've got exactly what you need here):
INNER JOIN Subjects sub ON sub.id = ss.subject_id AND PATINDEX ( ',%' + sub.id + '%,' ,@subject_list ) > 0
Note: if your comma-delimited list has potential extra spaces around each item that need to be removed (or something like that) you can take care of that sort of thing with some additional manipulation of the @subject_list variable before doing the join. Tedious, but not impossible.
[Please don't everybody tell me I should have changed the incoming information so I didn't have to do this... it wasn't an option. And it actually does work with reasonable speed, considering all the other factors in the scenario...]
>L<
November 25, 2006 at 9:58 am
Followup thought: I think in your situation you don't need the '%' wildcards in the PATINDEX expression, just the embracing commas.
Sorry if that was confusion/red herring.
>L<
November 25, 2006 at 11:31 am
Actually, PatIndex does need the wildcards as it performs a pattern search much like the keyword LIKE. Without the wildcards, the entire strings would have to be exact matches. You can leave out the wildcards if you use CharIndex instead of PatIndex.
The reason that the distinct doesn't work is because row_number() makes each row unique as each row has a different row_number.
November 25, 2006 at 12:42 pm
>>
Actually, PatIndex does need the wildcards as it performs a pattern search much like the keyword LIKE. Without the wildcards, the entire strings would have to be exact matches. You can leave out the wildcards if you use CharIndex instead of PatIndex.
<<
That's what I meant: he *can* use charindex instead of patindex in his situation, I bet. In my situation the strings within the delimited set could actually be partials, so the %'s and patindex are necessary... but it was muddying the waters to suggest that for his scenario, as I had originally done.
The point is that he has a delimited string in which he has to find a string, and that this approach *can* be used in a join expression. Which is what he wants to do, and which he apparently thought he couldn't do.
>>The reason that the distinct doesn't work
I have no idea what you're after regarding DISTINCT, that must be in response to something else in the thread. At least, I hope I didn't say anything about DISTINCT; I certainly didn't meant to, AAR!
>L<
November 25, 2006 at 8:46 pm
No, you didn't say distinct, but it is in his original query and is useless when you add something like row_number to the select list.
November 26, 2006 at 2:40 pm
Exactly! which was the problem, so I found that the joins were the issue here, and I needed to re-think the way my query was to work.
November 26, 2006 at 6:15 pm
>>so I found that the joins were the issue here, and I needed to re-think the way my query was to work.
OIC, sorry, then can you use PeterHe's EXISTS suggestion and bring the subjects condition in as you need to?
I see that you have suggested this, but I'm not sure what the problem is.
IOW, something like what I've cribbed together below using what he demonstrated *should* work for you (I'll use the CHARINDEX thing that seems appropriate for your comma-delimited set of subjects, with caveats as before that this might not be exactly the syntax you need for your conditions but that the UDF isn't necessary).
I don't have your data set but it seems to do the trick for me with similar one-to-many relationships...
SET @Subject_list = ',' + @Subject_list + ','
WITH SearchTable AS ( select distinct st.id, st.title, st.sub_title, st.synopsis, st.short_code, ROW_NUMBER() OVER (ORDER BY st.title asc) AS RowNumber from stock as st left join Keywords on Keywords.stock_id = st.id left join OnlinePreviews opLow ON opLow.stock_id = st.id and opLow.[type] = 1 left join OnlinePreviews opHigh ON opHigh.stock_id = st.id and opHigh.[type] = 2 left join TeachersNotes tn ON tn.stock_id = st.id
WHERE EXISTS(SELECT 1 FROM Stock_Subjects ss
INNER JOIN Subjects sub ON
ss.subject_id = sub.id AND
CHARINDEX(sub.id,@subject_list) > 0
WHERE ss.stock_id = st.id)
)
... ?
I know you said "Also it seems that when I use that Exists() function I can no longer join to the subjects table which is joined to the stock_subjects. Is there any way I can join to a table inside the Exists() function?"... but I'm not seeing the problem with doing this.
I apologize if I am misunderstanding something...
>L<
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply