Post reply

Best way to do same.

  • April 28, 2013 at 11:14 pm

    #276911

    create table main(fileNo Integer)

    insert into main values(1)

    insert into main values(2)

    insert into main values(3)

    insert into main values(4)

    insert into main values(5)

    insert into main values(6)

    create table doc(fileNo Integer, code char(1))

    insert into doc values(1,'I')

    insert into doc values(1,'A')

    insert into doc values(2,'A')

    insert into doc values(3,'I')

    insert into doc values(3,'A')

    insert into doc values(6,'A')

    select main.fileNo from main

    where fileno not in(select fileno from doc where code='I')

    I need records for which no corresponding record found in Doc Table with Code = 'I'

    so output here is:

    2

    4

    5

    6

    is there any better way to do the same?

    thanks

  • April 29, 2013 at 12:23 am

    #1610672

    Hi surindersinghthakur,

    insert into doc values(1,'A')

    insert into doc values(3,'A')

    the above records was not match with Code='I' in doc table right .but you are not displaying in query output result.those records you don't want or you want along with.

    --chalam

  • April 29, 2013 at 12:27 am

    #1610673

    I need only records from MAIN table for which it did not find respective record in DOC table with CODE = 'I'

    I re-wrote same query like below:

    select main.fileNo from main

    left join doc on main.fileno = doc.fileno and doc.code = 'I'

    where doc.code is null

    but not sure if there is any best way of doing it.

    Thanks

  • April 29, 2013 at 8:51 am

    #1610846

    One more way to do this..

    SELECT*

    FROMmain AS m

    WHERE NOT EXISTS( SELECT * FROM doc AS d WHERE m.fileno = d.fileno AND d.code = 'I' )

    We can't say that one method is definitely better than the other

    You can test all these options in your testing/development environment and see which gives the correct output and also performs well

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • April 29, 2013 at 8:53 am

    #1610847

    http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

    http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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