Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Smile Unanswered: How to compare value pair of one table with value pair of another table

    Here is the problem:
    -----------------------
    create table A( a1 int, a2 int)
    create table B( b1 int, b2 int)

    insert A values(1,1)
    insert A values(1,2)
    insert A values(2,1)
    insert A values(2,3)
    insert A values(3,1)
    insert A values(3,3)

    insert B values(1,1)
    insert B values(1,2)
    insert B values(3,2)
    insert B values(2,2)

    What is the SQL query to find out the pairs existing in B, but not in A ?

    I can solve this by using string functions, but my problem is I have to write a query that can execute on MSSQL, DB2 and ORACLE, and as you know they have differnt syntax for string functions.

    Thanks in advance.

    Regards,
    s99shah.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pairs in B but not in A? left outer join, check for unmatched rows --
    Code:
    select b1, b2
      from B
    left outer
      join A
        on b1 = a1
       and b2 = a2
     where a1 is null
    rudy
    http://r937.com/

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    TRY THIS

    Select B.* from a,b
    where (+) a1 = b1;
    SATHISH .

  4. #4
    Join Date
    Jun 2011
    Posts
    3

    More clean solution

    select b1, b2
    from b
    where not exists
    (
    select 1
    from a
    where a1 = b1 and a2 = b2
    )

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    A third solution just for completeness - I guess rudy's join version is the most effective one (although Oracle will most probably generate the same execution plan for both queries)
    Code:
    select b1, b2
    from b
    where (b1, b2) not in (select a1, a2 from a);

  6. #6
    Join Date
    Jun 2011
    Posts
    3

    That's not Tsql

    It's a mysql code very clean. Thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fgm1477 View Post
    That's not Tsql
    you're right, it isn't

    this is the ANSI SQL forum, not the Tsql forum

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2011
    Posts
    3
    I completly understood what you said. I think shammat posted a mysql code.


    Thanks anyway for the clarification.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fgm1477 View Post
    I think shammat posted a mysql code.
    um..... no

    he posted ANSI SQL code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The query from satish_ct is not standard SQL. Everything else is/was.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •