1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pl sql query to find matching columns.

Discussion in 'SQL PL/SQL' started by kruitzpola, Sep 14, 2010.

  1. kruitzpola

    kruitzpola Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Hi All

    I have view A and view B.

    I want a query to find 'account_no's existing in view A but NOT in B.

    I tried 'inner join' :

    (select * from DOCTABABOCOLD inner join PATIENTDATA on DOCTABABOCOLD."AcctNum" = PATIENTDATA."AcctNum") , but did not get the desired result...


    Please help quickly.....
     
  2. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    Have you tried using MINUS ?
     
    kruitzpola likes this.
  3. kruitzpola

    kruitzpola Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    MINUS requires the tables to have similar columns with similar data types. Unfortunately in my case, the view B has more fields than view A.

    An "AcctNum" present in B HAS to be in A. But "AcctNum" is unique in B and NOT in B. :(
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have the query in pseudocode in your problem statement so why haven't you written a NOT IN query:

    Code (SQL):
     
    SELECT * FROM DOCTABABOCOLD WHERE "AcctNum" NOT IN (SELECT "AcctNum" FROM PATIENTDATA);
     
     
    kruitzpola likes this.
  5. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    not necessary.

    specify the column name you want to find difference on.

    try like this
    Code (SQL):

    SELECT field1 FROM tab1
    minus
    SELECT field1 FROM tab2
     
    kruitzpola likes this.
  6. kruitzpola

    kruitzpola Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Thanks to the both of you!!!!

    Both of the queries work great..

    I'm new to pl/sql and so not aware of all the keywords, zargon.

    Thanks a lot to both of you...!!!
     
  7. sennen

    sennen Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    You can also use:
    Code (SQL):
    SELECT * FROM viewA WHERE NOT EXISTS (SELECT * FROM viewB WHERE viewA.AcctNum = viewB.AcctNum).
    This SELECT retreives only records that exist in viewA and not Exists in viewB using AcctNum as joining column.
     
  8. erpsindhu@gmail.com

    erpsindhu@gmail.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    75
    thanks for the replies