Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: Having rows with some null values returned conditionally

    This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:

    We are in the process of converting Access database to MSSQL with web form front ends.

    I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.

    I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.

    Here is a sample of the data:

    Code:
    SQL> select * from test;
    
    COL1  COL2  COL3  COL4
    ----- ----- ----- -----
    this  is    a     test
    this  is    not   test
    this  is    not
    this  is    test  too
          is    test  too
          is          too
          is          too
    
    7 rows selected.
    Now, if I have this SQL run, it will return only rows that have no nulls in any columns:

    Code:
    select
    
    col1, col2, col3, col4
    from test where
    col1 like'th%' and col2 like '%' and col3 like '%' and col4 like '%'
    ; COL1 COL2 COL3 COL4 ----- ----- ----- ----- this is a test this is not test this is test too
    Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:

    Code:
    select
    
    col1, col2, col3, col4
    from test where
    (col1 like'th%' OR col1 is null) and (col2 like '%' OR col2 is null) and (col3 like '%' OR col3 is null) and (col4 like '%' OR col4 is null)
    ; COL1 COL2 COL3 COL4 ----- ----- ----- ----- this is a test this is not test this is not this is test too is test too is too is too
    The idea is to only select the first 4 rows in the above example.

    I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.

    Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?

    thanks in advance

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    create table test1
    (col1 varchar(5),
     col2 varchar(5),
     col3 varchar(5),
     col4 varchar(5))
     
    insert into test1 values 
    	('this', 'is', 'a', 'test'),
    	('this', 'is', 'not', 'test'),
    	('this', 'is', 'not', null),
    	('this', 'is', 'test', 'too'),
    	(null, 'is', 'test', 'too'),
    	(null, 'is', null, 'too'),
    	(null, 'is', null, 'too')
    
    
    declare @in1 varchar(20) = 'th',
    	@in2 varchar(20), 
    	@in3 varchar(20),
    	@in4 varchar(20)
    
    select
        col1, col2, col3, col4
    from test1
    where (@in1 is not null and col1 like '%' + @in1 + '%') 
       or (@in2 is not null and col2 like '%' + @in2 + '%') 
       or (@in3 is not null and col3 like '%' + @in3 + '%') 
       or (@in4 is not null and col4 like '%' + @in4 + '%')

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    Quote Originally Posted by MCrowley View Post
    Code:
    create table test1
    (col1 varchar(5),
     col2 varchar(5),
     col3 varchar(5),
     col4 varchar(5))
     
    insert into test1 values 
    	('this', 'is', 'a', 'test'),
    	('this', 'is', 'not', 'test'),
    	('this', 'is', 'not', null),
    	('this', 'is', 'test', 'too'),
    	(null, 'is', 'test', 'too'),
    	(null, 'is', null, 'too'),
    	(null, 'is', null, 'too')
    
    
    declare @in1 varchar(20) = 'th',
    	@in2 varchar(20), 
    	@in3 varchar(20),
    	@in4 varchar(20)
    
    select
        col1, col2, col3, col4
    from test1
    where (@in1 is not null and col1 like '%' + @in1 + '%') 
       or (@in2 is not null and col2 like '%' + @in2 + '%') 
       or (@in3 is not null and col3 like '%' + @in3 + '%') 
       or (@in4 is not null and col4 like '%' + @in4 + '%')
    Thanks much!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Glenstr,

    If
    Code:
    where
         col1 like 'th%' 
     and col2 like '%'
     and col3 like 't%'
     and col4 like 't%'
    ;
    I expected the result might be...
    Code:
    COL1  COL2  COL3  COL4
    ----- ----- ----- -----
    this  is    test  too
    Is it right?

    Or, should the result be...
    Code:
    COL1  COL2  COL3  COL4
    ----- ----- ----- -----
    this  is    not   test
    this  is    test  too
    Or, any other result?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another case.

    If

    Code:
    where
         col1 like 'th%' 
     and col2 like '%'
     and col3 like 't%'
     and col4 like '%'
    ;
    I expected the result might be...

    Code:
    COL1  COL2  COL3  COL4
    ----- ----- ----- -----
    this  is    test  too
    Is it right?

    Or, should the result be...

    Code:
    COL1  COL2  COL3  COL4
    ----- ----- ----- -----
    this  is    not   test
    this  is    not
    this  is    test  too
    Or, any other result?

  6. #6
    Join Date
    Aug 2012
    Posts
    4
    Thanks Tonkuma - I think I almost have it here, I applied MCrowley's advice to my real world code and it works if I only have one criteria, but when I enter two or more returns all rows, so if a user populated only one field on the form it would be ok, but if more than one then not ok.

    Here is the production code:

    Code:
     declare 
    	@in1 varchar(80),
    	@in2 varchar(80) = '030', 
    	@in3 varchar(80) ,
    	@in4 varchar(80) ,
    	@in5 varchar(80) , 
    	@in6 varchar(80) ,
    	@in7 varchar(80) ,
    	@in8 varchar(80) , 
    	@in9 varchar(80) ,
    	@in10 varchar(80) ,
    	@in11 varchar(80) , 
    	@in12 varchar(80) 
    
    SELECT 
    ISNULL([INSTRUMENT INDEX].AREA,'n/a') as AREA, 
    ISNULL([INSTRUMENT INDEX].LOOP,'n/a') as LOOP, 
    ISNULL([INSTRUMENT INDEX].[INSTRUMENT TAG],'n/a') as [INSTRUMENT TAG], 
    ISNULL([INSTRUMENT INDEX].[INSTRUMENT DESCRIPTION],'n/a') as [INSTRUMENT DESCRIPTION],
    ISNULL([INSTRUMENT INDEX].SUPPLIER,'n/a')as SUPPLIER, 
    ISNULL([INSTRUMENT INDEX].MANUFACTURER,'n/a') as MANUFACTURER,
    ISNULL([INSTRUMENT INDEX].MODEL,'n/a') as MODEL,
    ISNULL([INSTRUMENT INDEX].[P&C DRAWING],'n/a') as [P&C DRAWING],
    ISNULL([INSTRUMENT INDEX].[LOCATION DRAWING],'n/a') as [LOCATION DRAWING], 
    ISNULL([INSTRUMENT INDEX].LOCATION,'n/a') as LOCATION, 
    ISNULL([INSTRUMENT INDEX].[DATA SHEET TYPE],0) as [DATA SHEET TYPE], 
    ISNULL([INSTRUMENT INDEX].[EP/WO],'n/a') as [EP/WO] 
    FROM   [INSTRUMENT INDEX] 
    
           LEFT JOIN [DSTYPE FIELD HEADINGS] 
                  ON [INSTRUMENT INDEX].[DATA SHEET TYPE] = 
                     [DSTYPE FIELD HEADINGS].[DATA SHEET TYPE] 
    WHERE  
    	(@in1 is not null and [INSTRUMENT INDEX].AREA LIKE '%' + @in1 + '%')
           or (@in2 is not null and [INSTRUMENT INDEX].LOOP LIKE '%' + @in2 + '%')
           or (@in3 is not null and [INSTRUMENT INDEX].[INSTRUMENT TAG] like '%' + @in3 +'%')
           or (@in4 is not null and [INSTRUMENT INDEX].[INSTRUMENT DESCRIPTION] like '%' + @in4 +'%')
           or (@in5 is not null and [INSTRUMENT INDEX].SUPPLIER like '%' + @in5 +'%')
           or (@in6 is not null and [INSTRUMENT INDEX].MANUFACTURER like '%' + @in6 +'%') 
           or (@in7 is not null and [INSTRUMENT INDEX].MODEL like '%' + @in7 +'%')
           or (@in8 is not null and [INSTRUMENT INDEX].[P&C DRAWING] like '%' + @in8 +'%') 
           or (@in9 is not null and [INSTRUMENT INDEX].[LOCATION DRAWING] like '%' + @in9 +'%') 
           or (@in10 is not null and [INSTRUMENT INDEX].LOCATION like '%' + @in10 +'%')
           or (@in11 is not null and CONVERT(NVARCHAR, [INSTRUMENT INDEX].[DATA SHEET TYPE]) like '%' + @in11 +'%') 
           or (@in12 is not null and [INSTRUMENT INDEX].[EP/WO] like '%' + @in12 +'%')
    Now this returns all the rows with 030 in the "LOOP" column, including those with nulls in other columns, but as soon as I add a second criteria it will return all rows, so I don't have it quite figured out yet.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There are two issues.

    (1) "and" and "or" might be reversed.
    Code:
    WHERE  
          (@in1 is null OR [INSTRUMENT INDEX].AREA LIKE '%' + @in1 + '%')
      AND (@in2 is null OR [INSTRUMENT INDEX].LOOP LIKE '%' + @in2 + '%')
      AND (@in3 is null OR [INSTRUMENT INDEX].[INSTRUMENT TAG] like '%' + @in3 +'%')
    ...
    (2) Do you want to search the input string in anyplace of the column, or in leading string of the column?
    '%' + @in1 + '%'
    or
    @in1 + '%'

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE test1 
    (  col1 VARCHAR(5)
    ,  col2 VARCHAR(5)
    ,  col3 VARCHAR(5)
    ,  col4 VARCHAR(5)
    )
     
    INSERT INTO test1 VALUES 
       ('this', 'is', 'a',    'test')
    ,  ('this', 'is', 'not',  'test')
    ,  ('this', 'is', 'not',  null)
    ,  ('this', 'is', 'test', 'too')
    ,  (null,   'is', 'test', 'too')
    ,  (null,   'is', null,   'too')
    ,  (null,   'is', null,   'too')
    
    
    DECLARE
       @in1 VARCHAR(20) = 'th'
    ,  @in2 VARCHAR(20)
    ,  @in3 VARCHAR(20)
    ,  @in4 VARCHAR(20)
    
    SELECT col1, col2, col3, col4
       FROM test1
       WHERE  (@in1 is null or col1 like '%' + @in1 + '%') 
          AND (@in2 is null or col2 like '%' + @in2 + '%') 
          AND (@in3 is null or col3 like '%' + @in3 + '%') 
          AND (@in4 is null or col4 like '%' + @in4 + '%')
    
    DROP TABLE test1
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note that this will perform very poorly if parameterized (such as if it were made into a stored procedure).
    Code:
    CREATE TABLE test1 
    (  col1 VARCHAR(5)
    ,  col2 VARCHAR(5)
    ,  col3 VARCHAR(5)
    ,  col4 VARCHAR(5)
    )
     
    INSERT INTO test1 VALUES 
       ('this', 'is', 'a',    'test')
    ,  ('this', 'is', 'not',  'test')
    ,  ('this', 'is', 'not',  null)
    ,  ('this', 'is', 'test', 'too')
    ,  (null,   'is', 'test', 'too')
    ,  (null,   'is', null,   'too')
    ,  (null,   'is', null,   'too')
    
    
    DECLARE
       @in1 VARCHAR(20) = 'th'
    ,  @in2 VARCHAR(20)
    ,  @in3 VARCHAR(20)
    ,  @in4 VARCHAR(20)
    
    SELECT col1, col2, col3, col4
       FROM test1
       WHERE  (@in1 is null or col1 like '%' + @in1 + '%') 
          AND (@in2 is null or col2 like '%' + @in2 + '%') 
          AND (@in3 is null or col3 like '%' + @in3 + '%') 
          AND (@in4 is null or col4 like '%' + @in4 + '%')
    
    DROP TABLE test1
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Aug 2012
    Posts
    4
    thanks Pat, tonkuma & MCrowley, I believe it's now solved, because the developer said the form had to pass the % wild card to the query, I modified the query to look like this (with example criteria) and it appears to be delivering what we want.

    Code:
    declare 
    	@in1 varchar(80) = '%',
    	@in2 varchar(80) = '030', 
    	@in3 varchar(80) = '%' ,
    	@in4 varchar(80) = '%' ,
    	@in5 varchar(80) = '723', 
    	@in6 varchar(80) = '%' ,
    	@in7 varchar(80) = '%' ,
    	@in8 varchar(80) = '%' , 
    	@in9 varchar(80) = '%' ,
    	@in10 varchar(80) = '%' ,
    	@in11 varchar(80) = '%' , 
    	@in12 varchar(80) = '%' 
    
    SELECT [INSTRUMENT INDEX].AREA, 
                    [INSTRUMENT INDEX].LOOP, 
                    [INSTRUMENT INDEX].[INSTRUMENT TAG], 
                    [INSTRUMENT INDEX].[INSTRUMENT DESCRIPTION], 
                    [INSTRUMENT INDEX].SUPPLIER, 
                    [INSTRUMENT INDEX].MANUFACTURER, 
                    [INSTRUMENT INDEX].MODEL, 
                    [INSTRUMENT INDEX].[P&C DRAWING], 
                    [INSTRUMENT INDEX].[LOCATION DRAWING], 
                    [INSTRUMENT INDEX].LOCATION, 
                    [INSTRUMENT INDEX].[DATA SHEET TYPE], 
                    [INSTRUMENT INDEX].[EP/WO] 
    
    FROM   [INSTRUMENT INDEX] 
    
           LEFT JOIN [DSTYPE FIELD HEADINGS] 
                  ON [INSTRUMENT INDEX].[DATA SHEET TYPE] = 
                     [DSTYPE FIELD HEADINGS].[DATA SHEET TYPE] 
    WHERE  
    	(@in1 = '%' or [INSTRUMENT INDEX].AREA LIKE '%' + @in1 + '%')
           and (@in2 = '%' or [INSTRUMENT INDEX].LOOP LIKE '%' + @in2 + '%')
           and (@in3 = '%' or [INSTRUMENT INDEX].[INSTRUMENT TAG] like '%' + @in3 +'%')
           and (@in4 = '%' or [INSTRUMENT INDEX].[INSTRUMENT DESCRIPTION] like '%' + @in4 +'%')
           and (@in5 = '%' or [INSTRUMENT INDEX].SUPPLIER like '%' + @in5 +'%')
           and (@in6 = '%' or [INSTRUMENT INDEX].MANUFACTURER like '%' + @in6 +'%') 
           and (@in7 = '%' or [INSTRUMENT INDEX].MODEL like '%' + @in7 +'%')
           and (@in8 = '%' or [INSTRUMENT INDEX].[P&C DRAWING] like '%' + @in8 +'%') 
           and (@in9 = '%' or [INSTRUMENT INDEX].[LOCATION DRAWING] like '%' + @in9 +'%') 
           and (@in10 = '%' or [INSTRUMENT INDEX].LOCATION like '%' + @in10 +'%')
           and (@in11 = '%' or CONVERT(NVARCHAR, [INSTRUMENT INDEX].[DATA SHEET TYPE]) like '%' + @in11 +'%') 
           and (@in12 = '%' or [INSTRUMENT INDEX].[EP/WO] like '%' + @in12 +'%')
    Again, thanks a lot for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •