SELECT CASE T-SQL

  • 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

  • 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);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • cool that worked

    the trick is the LEFT OUTER JOIN eh?

  • Johann Montfort (4/14/2009)


    cool that worked

    the 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);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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