Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: query on more tables

    Hi,
    I've 2 tables:
    TABLE AX:
    ID NUMBER PK

    TABLE BX:
    ID NUMBER FK

    TABLE A HAS 2000 RECORDS
    TABLE B HAS 3500 RECORDS

    I'd like to write a query to find 1500 record not equal.

    I tried this:
    select a.id, b.id
    from ax a, bx b
    where a.id <> b.id
    group by a.id, b.id


    but It not run correctly

    How can I write this query??

    Thanks
    Raf

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Raf,

    -- in a but not b
    select id from tbla
    minus
    select id from tblb

    -- in b but not a
    select id from tblb
    minus
    select id from tbla

    you could 'union all' the two results to give all id in one table but not the other.

    Hth
    Bill

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: query on more tables

    Originally posted by raf
    Hi,
    I've 2 tables:
    TABLE AX:
    ID NUMBER PK

    TABLE BX:
    ID NUMBER FK

    TABLE A HAS 2000 RECORDS
    TABLE B HAS 3500 RECORDS

    I'd like to write a query to find 1500 record not equal.

    I tried this:
    select a.id, b.id
    from ax a, bx b
    where a.id <> b.id
    group by a.id, b.id


    but It not run correctly

    How can I write this query??

    Thanks
    Raf
    Is the ID column in table BX a Foreign Key to the ID column in table AX, as your listing suggests? If this is the case, then you have multiple rows in table BX that correspond to a single row in table AX? Could this be possible.

    If so, try a query like the following:

    select b.id
    from ax a,
    bx b
    where a.id = b.id
    group by b.id
    having (count(b.id) > 1;

    If the above assumptions (PK and FK) are not correct, then a more correct answer has already been posted.

    HTH,
    Patrick

Posting Permissions

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