
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!

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

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!

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 nonequality in the given three queries.

Re: Re: How to write this query
Originally posted by engiri
Where are you comparing the values of a and b for nonequality 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?

Is there a way I can display every distinct b value with every a value?
Thanks!

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.

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!

Isn't a good idea to post with sample data what you are looking for?
Tamil

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.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
