Post reply

omit part of WHERE clause?

  • July 21, 2005 at 8:07 am

    #165425

    Have two tables tblpart (participants) and tblorg (organisations), NOT all tblpart entries have a corresponding tblorg entry, using the query below I want to return all the entries in tblpart when @varCat = null regardless of whether or not they have a corresponding tblorg entry, at the moment only those with one are returned.  Is it possible to omit part of a where clause if a variable meets a certain condition ie null?

    declare @varCat

    set @varCat = null

    select *

    from tblpart left outer join tblorg on tblpart.orgid = tblorg.orgid

    tblorg.catid = case when @varCat is null then tblorg.catid else @varCat end

    Any help would be appreciated

  • July 21, 2005 at 8:39 am

    #576098

    Could you post some sample data and the output you want (for different @varCat values).

     

  • July 21, 2005 at 9:21 am

    #576121

    select *

    from tblpart left outer join tblorg on tblpart.orgid = tblorg.orgid

    where (tblorg.catid = @varCat or  @varCat is null )

     

    Just keep in mind that you may get Table Scans with this

     


    * Noel

  • July 21, 2005 at 9:27 am

    #576123

    Thanks Noel, your a legend.

  • July 21, 2005 at 9:31 am

    #576126

    happy to help


    * Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply