Post reply

A Not Equal to Query Statement

  • July 20, 2009 at 2:48 pm

    #202691

    I have 2 tables I'm trying to join. I need to run a query to display every vendor(t1.vendorid) and vendor address code (t1.vac1) in table1 that is not equal to the vendor(t2.vendorid) and vendor address code(t2.vac2) up in table2. For some reason I keep getting duplication.

    sorry I'm new at this.

    Thank you,

  • July 20, 2009 at 2:51 pm

    #1027295

    Is this what you are looking for?

    Select t1.vendorid,t1.vac1 from table1

    Except

    Select t2.vendorid,t2.vac2 from table2

  • July 20, 2009 at 3:30 pm

    #1027310

    Thanks you its working so far.

  • July 21, 2009 at 12:36 am

    #1027427

    You can get same performance with :

    SELECT t1.vendorid,t1.vac1 from table1

    WHERE t1.vendorid not in (SELECT t2.vendorid from table2)

    AND t1.vac1 not in (SELECT t2.vac2 from table2)

  • July 21, 2009 at 8:28 am

    #1027651

    Thanks, I guess its always two ways to skin a cat.

  • July 21, 2009 at 9:06 am

    #1027691

    Here is a third way to skin a cat:

    select

    t1.vendorid,

    t1.vac1

    from

    table1 t1

    left outer join table2 t2

    on (t1.vendorid = t2.vendorid

    and t1.vac1 = t2.vac2)

    where

    t2.vendorid is null;

  • July 21, 2009 at 3:34 pm

    #1027964

    It depends on what you want exactly. Of the three versions listed you will get two different results sets back..DECLARE @Table1 TABLE (VendorID INT, Vac1 INT)

    INSERT @Table1

    SELECT 1, 1

    UNION ALL SELECT 1, 2

    UNION ALL SELECT 2, 3

    UNION ALL SELECT 3, 7

    UNION ALL SELECT 4, 9

    DECLARE @Table2 TABLE (VendorID INT, Vac2 INT)

    INSERT @Table2

    SELECT 1, 1

    UNION ALL SELECT 1, 2

    UNION ALL SELECT 2, 5

    UNION ALL SELECT 3, 7

    --Wesley's Version

    Select t1.vendorid,t1.vac1 from @table1 AS t1

    Except

    Select t2.vendorid,t2.vac2 from @table2 AS t2

    -- RESULTS

    --vendorid vac1

    ------------- -----------

    --2 3

    --4 9

    --(2 row(s) affected)

    --@SQL's version

    SELECT t1.vendorid,t1.vac1 from @table1 AS t1

    WHERE t1.vendorid not in (SELECT t2.vendorid from @table2 AS t2)

    AND t1.vac1 not in (SELECT t2.vac2 from @table2 AS t2)

    -- RESULTS

    --vendorid vac1

    ------------- -----------

    --4 9

    --(1 row(s) affected)

    --Lynn's version

    select

    t1.vendorid,

    t1.vac1

    from

    @table1 t1

    left outer join @table2 t2

    on (t1.vendorid = t2.vendorid

    and t1.vac1 = t2.vac2)

    where

    t2.vendorid is null

    -- RESUTLS

    --vendorid vac1

    ------------- -----------

    --2 3

    --4 9

    --(2 row(s) affected)

  • July 21, 2009 at 3:54 pm

    #1027974

    The problem with @sql's version is that the AND in the WHERE clause should be an OR. Change it and check out the results.

  • July 22, 2009 at 9:41 am

    #1028440

    Lynn,

    You are correct the Or in the SQL verison made a big difference in the result.

    Thanks all for your help I really appreciate it.

Viewing 9 posts - 1 through 8 (of 8 total)

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