April 14, 2009 at 6:19 am
HI All,
I have the following code at the moment
SELECT * FROM
(
SELECT vidID, vidDate, vidTitle, vidDesc, AddedBy, OnlyForMembers, ViewCount,
ROW_NUMBER() OVER (ORDER BY vidDate DESC) AS RowNum
FROM HS_Videos hv INNER JOIN HS_Video_Tags hvt ON hv.vidID = hvt.fk_vidID WHERE ((@TagID = 0) or (@TagID <> 0 and hvt.fk_tagId = @TagID))
) Videos
WHERE Videos.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
ORDER BY vidDate DESC
When the TagID > 0, it works fine, however when the TagID = 0, then its displaying the same video according the the number of tags is has, because of the INNER JOIN. Is it possible to do a CASE or IF Clause, to specify that if the TagID = 0, then ommit the INNER JOIN, and do a normal select?
Thanks for your help and time
April 14, 2009 at 6:42 am
Johann Montfort (4/14/2009)
HI All,I have the following code at the moment
SELECT * FROM
(
SELECT vidID, vidDate, vidTitle, vidDesc, AddedBy, OnlyForMembers, ViewCount,
ROW_NUMBER() OVER (ORDER BY vidDate DESC) AS RowNum
FROM HS_Videos hv INNER JOIN HS_Video_Tags hvt ON hv.vidID = hvt.fk_vidID WHERE ((@TagID = 0) or (@TagID <> 0 and hvt.fk_tagId = @TagID))
) Videos
WHERE Videos.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
ORDER BY vidDate DESC
When the TagID > 0, it works fine, however when the TagID = 0, then its displaying the same video according the the number of tags is has, because of the INNER JOIN. Is it possible to do a CASE or IF Clause, to specify that if the TagID = 0, then ommit the INNER JOIN, and do a normal select?
Thanks for your help and time
Try this:
SELECT * FROM
(
SELECT vidID, vidDate, vidTitle, vidDesc, AddedBy, OnlyForMembers, ViewCount,
ROW_NUMBER() OVER (ORDER BY vidDate DESC) AS RowNum
FROM HS_Videos hv LEFT OUTER JOIN HS_Video_Tags hvt ON hv.vidID = hvt.fk_vidID AND hvt.fk_tagId = @TagID
WHERE @TagID=0 OR NOT (hvt.fk_tagId IS NULL)
) Videos
WHERE Videos.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
ORDER BY vidDate DESC
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
April 14, 2009 at 6:46 am
cool that worked
the trick is the LEFT OUTER JOIN eh?
April 14, 2009 at 6:51 am
Johann Montfort (4/14/2009)
cool that workedthe trick is the LEFT OUTER JOIN eh?
Yes, because with that join you get all the records from the left (or base) table and can then filter the results if required (@TagID<>0) on the lookup.
Of course, depending on how much data you have, you may want to have two queries - one for if the TagID is zero , one for not, so that you get the best performance when doing a tag lookup.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
April 14, 2009 at 6:59 am
Yes I was thinking of that, performance wise it will be better 2 stored procs.
However, I am not envisaging that I will have lots of data, if i will do I will do the necessary changes, for the time being this works perfect
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply