Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Unanswered: SQL Query -- Help

    Can a SQL query be written to retrieve the results satisfying the following criteria:

    Table a: Count 3
    id value
    10 xxx
    20 yyy
    30 zzz

    Table b: Count 5
    id value
    10 xxx
    20 yyy
    30 zzz
    40 aaa
    50 bbb

    The desired result should be all the id's with the above result. i.e; inequal count.
    I would like to disregard all the id's with same count in both the tables.

    Please help.

    Thanks for your help.

    Regards,
    namoh

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    select a.id, a.value
    from TableA a,
    (select a2.id, count(*) As acnt from TableA a2 group by a2.id) inline_view_a,
    (select b.id, count(*) As bcnt from TableB b group by b.id) inline_view_b
    WHERE a.id = inline_view_a.id AND
    inline_view_a.id = inline_view_b.id AND
    inline_view_a.acnt <> inline_view_b.bcnt

    union all

    select a.id, a.value
    from TableA a
    where not exists (select 1 from TableB b where b.id = a.id)

    union all

    select b.id, b.value
    from TableB b
    where not exists (select 1 from TableA a where b.id = a.id)


    I haven't written an inline view yet myself, but from what I've read, this should work. First query gets all those recs which match up by id in both tables, but have different total counts, and the second two queries grab those recs that have no match in the other table, hence differing counts.


    -cf

  3. #3
    Join Date
    Feb 2004
    Posts
    16
    Thank You very much.

    Originally posted by chuck_forbes
    select a.id, a.value
    from TableA a,
    (select a2.id, count(*) As acnt from TableA a2 group by a2.id) inline_view_a,
    (select b.id, count(*) As bcnt from TableB b group by b.id) inline_view_b
    WHERE a.id = inline_view_a.id AND
    inline_view_a.id = inline_view_b.id AND
    inline_view_a.acnt <> inline_view_b.bcnt

    union all

    select a.id, a.value
    from TableA a
    where not exists (select 1 from TableB b where b.id = a.id)

    union all

    select b.id, b.value
    from TableB b
    where not exists (select 1 from TableA a where b.id = a.id)


    I haven't written an inline view yet myself, but from what I've read, this should work. First query gets all those recs which match up by id in both tables, but have different total counts, and the second two queries grab those recs that have no match in the other table, hence differing counts.


    -cf

Posting Permissions

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