none
sql query where condition to negate RRS feed

  • Question

  • Hi,

    I need to get result of select query in such a way that it should not get those type of records.

    For example,

    SELECT MainType, SubType, Category, SecID

    FROM Table1

    WHERE

      -- I should filter the data that mean if combination of below is values, do not get them

    If MainType = 'Fruit' AND SubType = 'Hanging' AND Category = 'seasonal' - eliminate

    If MainType = 'Fruit' AND 'SubType = 'Creeper' AND Category = 'seasonal' - eliminate

    If MainType = 'Vegetable' AND 'SubType' = 'Root' AND Category = 'Yearly' - elminate

    I have so many of these in where conditions to eliminate. How can I do it

    Wednesday, August 21, 2019 2:22 AM

All replies

  • Hi,

    Try:

    SELECT MainType, SubType, Category, SecID
    FROM Table1
    WHERE charindex(MainType+SubType+Category,'FruitHangingSeasonal') = 0
    and charindex(MainType+SubType+Category,'FruitCreeperSeasonal') = 0
    and charindex(MainType+SubType+Category,'VegetableRootYearly') = 0

    Do you have more conditions than three? Then you need to create a table for elimination and check for existence etc.

    Mark as answer if it helps. Thanks.


    Wednesday, August 21, 2019 3:15 AM
  • Hi Spunny,

     

    Please try following script.

      
    IF OBJECT_ID('test') IS NOT NULL drop table  test 
    go 
    create table test(
    ID int,
    MainType varchar(10),
    subtype varchar(10),
    Category varchar(10))
    insert into test values 
    (1,'Fruit', 'Hanging', 'seasonal'),
    (2,'Fruit', 'Creeper', 'seasonal'),
    (3,'Vegetable', 'SubType', 'Root'),
    (4,'Fruit', 'SubType', 'Root')
    
    --------create a table to save filter value 
    IF OBJECT_ID('filter') IS NOT NULL drop table  filter 
    go
    create table filter
    (MainType varchar(10),
    subtype varchar(10),
    Category varchar(10))
    insert into filter values 
    ('Fruit', 'Hanging', 'seasonal'),
    ('Fruit', 'Creeper', 'seasonal'),
    ('Vegetable', 'SubType', 'Root')
    
    select * from test a 
    where not exists(
    Select 1
    From test t
    inner Join filter lt On lt.MainType = t.MainType
    And lt.SubType = t.SubType
    And lt.Category = t.Category
    where t.id=a.id)
    
    /*
    ID          MainType   subtype    Category
    ----------- ---------- ---------- ----------
    4           Fruit      SubType    Root
    */


    If above sample doesn’t satisfy your requirement, please share us your table structure and some sample data along with your expected result. So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, August 21, 2019 6:23 AM
  • Try this..

    create table test(
    ID int,
    MainType varchar(10),
    subtype varchar(10),
    Category varchar(10))
    insert into test values 
    (1,'Fruit', 'Hanging', 'seasonal'),
    (2,'Fruit', 'Creeper', 'seasonal'),
    (3,'Vegetable', 'Root', 'Yearly'),
    (4,'Vegetable', 'Root', 'seasonal')
    
    
    select * from test where not((MainType = 'Fruit' AND SubType = 'Hanging' AND Category = 'seasonal') or
    	                          (MainType = 'Fruit' AND SubType = 'Creeper' AND Category = 'seasonal') or
    			          (MainType = 'Vegetable' AND SubType = 'Root' AND Category = 'Yearly'))

    Wednesday, August 21, 2019 8:25 AM