How to write this query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to write this query

  1. #1
    Join Date
    Jun 2000
    Posts
    315

    How to write this query

    There are two columns a and b in a table c. Every value a could have many same b values and every b value could have many a values. It's like a many to many relationship. Now I need to find out every a value who has different b values.
    How could I using sql to accomplish this?

    Thank you!

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    Select   a
    From     c
    Group By a
    Having   Count(Distinct b) > 1
    If a and b make a unique key on c then ...

    Code:
    Select   a
    From     c
    Group By a
    Having   Count(*) > 1
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2000
    Posts
    315
    Thank you!

    I did the same thing: select a, count(distinct b)
    from c
    group by a
    having count(distinct b) > 1;

    The problem is I also need b to be displayed with every a. How could I accomplish that?

    Thanks again!

  4. #4
    Join Date
    Apr 2003
    Posts
    353

    Re: How to write this query

    Originally posted by lgaorcl
    Now I need to find out every a value who has different b values.
    How could I using sql to accomplish this?

    Where are you comparing the values of a and b for non-equality in the given three queries.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Re: How to write this query

    Originally posted by engiri
    Where are you comparing the values of a and b for non-equality in the given three queries.
    GROUP BY + HAVING COUNT(DISTINCT)>0
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2000
    Posts
    315
    Is there a way I can display every distinct b value with every a value?

    Thanks!

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    select distinct a,b
    from     c
    where    a in
             (
             Select   a
             From     c
             Group By a
             Having   Count(Distinct b) > 1
             );
    There's some other ways this could be done, with analytic functions, for example, but I'd just stick to this one right now.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jun 2000
    Posts
    315
    Slimdave:

    Thank you! This is a good one! Could you tell me the other ways you mentioned? Like analytic functions?
    I am a sql learner!

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Isn't a good idea to post with sample data what you are looking for?

    Tamil

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by lgaorcl
    Slimdave:

    Thank you! This is a good one! Could you tell me the other ways you mentioned? Like analytic functions?
    I am a sql learner!
    Code:
    Select Distinct a,b
    From   c c1
    Where  Exists
           (
           Select 1 From c c2
           Where  c2.a  = c1.a
           And    c2.b != c1.b
           )
    /
    
    Select Distinct a,b
    From
       (
       Select a, b, count(Distinct b) Over (Partition By a) b#
       From   c
       )
    Where
       b# > 1
    /
    In SQL*Plus use the command "set autotrace on" to view the execution plan plus some execution statistics, so you can benchmark these different aproaches in different circumstances and chose the one best suited.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width