Re: Complicated query: searching for results within results.

"phreaker" <ph_reaker_at_hotmail.com> a écrit dans le message de news: 1163816958.841054.300200_at_e3g2000cwe.googlegroups.com...
| Hi, I have a query that I need to do that I've been thinking about, and
| I am stuck at this point.
|
| Table 1 has columns A, B, and C
|
| For a given A, I need to figure out whether there exists <B,C> pairs
| that have the same B, but different C. For a given A, there are a
| potentially many Bs and many Cs.
|
|
| So for a given A, if there are these results in the table:
|
| B C
| 1 5
| 1 100
| 2 4
| 2 4
|
| I need to return true, since <1,5> and <1,100> have same B, but
| different C.
|
| Thanks!
| David
|

SQL> select * from table_1;

    A B C
----- ----- -----

    1     1     5
    1     1   100
    1     2     4
    1     2     4
    2     1     5
    2     1   100

6 rows selected.

Just following what you said: for each A I want B associated with more than one distinct C.

SQL> select a, b, count(distinct c) nb_c   2 from table_1
  3 group by a, b
  4 having count(distinct c) > 1
  5 order by a, b
  6 /
    A B NB_C
----- ----- -----

    1     1     2
    2     1     2

2 rows selected.

Regards
Michel Cadot Received on Sat Nov 18 2006 - 01:05:53 CST