Post reply

Select where statement issue

  • September 1, 2011 at 12:01 pm

    #390325

    Hi Folks,

    select * from Form where FromID < > 3 and FromID < > 4

    Above SQL Query returned values except 3 number from fromId column but,it doesn't return values except 4 number.How can I modify my SQL Query?BTW,the column is not null.

    Regards,

  • September 1, 2011 at 12:19 pm

    #1376108

    What doesn't work as expected?

    The following code snippet does neither return 3 nor 4:

    DECLARE @form TABLE (FromID INT)

    INSERT INTO @form VALUES (1),(2),(3),(4),(5)

    SELECT *

    FROM @form

    WHERE FromID <> 3 AND FromID <> 4


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 2, 2011 at 9:40 am

    #1376518

    Hi

    I think this would be a better bet

    select * from Form where FromID not in(3,4)

    Thanks

    Salil

  • September 2, 2011 at 9:48 am

    #1376526

    Salil-386686 (9/2/2011)

    Hi

    I think this would be a better bet

    select * from Form where FromID not in(3,4)

    Thanks

    Salil

    I recommend to run both versions and compare the exection plans... 😉

    (Hint: FromID not in(3,4) will be translated into [FromID]<>(3) AND [FromID]<>(4) ) 😎


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 2, 2011 at 10:23 am

    #1376551

    Salil-386686 (9/2/2011)

    Hi

    I think this would be a better bet

    select * from Form where FromID not in(3,4)

    Thanks

    Salil

    Clearly, this is the way to do it:

    select f.*

    from @form f left join

    (select 3 a union all select 4 ) x on f.FromID = x.a

    where x.a is null

    *edit: using Lutz's @form table 😉

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • September 2, 2011 at 10:33 am

    #1376560

    Let's throw in some "modern" commands (not that they do anything good to the performance).

    SELECT *

    FROM @form

    except

    SELECT *

    FROM @form

    WHERE FromID = 3 OR FromID = 4

    Note to OP: apologies we're making fun out of your question. It's not personal. If you haven't had your question answered properly, don't hesitate to ask for further explanation.


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • September 2, 2011 at 10:38 am

    #1376563

    yep...but just thought that is more simple 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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