Count / group by 1

[stiley]

Hi there,
Another plain SQL question. I have data that holds entity relationships across years. From it I'd like to know which values of Entity2 have only ever been related to a single Entity1 regardless of the year.

Data looks like the following...

Entity1 Entity2 Year
443 444 1998
4685 4684 1999
4689 4684 1999
8007 16355 2009
8007 16355 2010
8007 16355 2011
8007 16355 2012
363914 37491 2012
146231 37491 2011
363914 37491 2008
363914 37491 2009
363914 37491 2011
146231 37491 2012
363914 37491 2006
363914 37491 2010

I expect 444, 16355 in the result but I just can not figure out how to write the select.
Any insight is much appreciated

DB - Oracle 10G

Thanks
Sean

 

[JarlH]

[tt]select distinct entity2
from st
where entity2 not in
(select entity2 from st
group by entity2,"year"
having count(*) > 1);[/tt]

Note: YEAR is a reserved word in ANSI SQL, thats why its "quoted" above.

 

[PHV]

SELECT Entity2 FROM yourTable
GROUP BY Entity1,Entity2
HAVING COUNT(*)=1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JarlH]

I have now simplified my example:[tt]
SQL>select entity2 from st
SQL&group by entity2 having count(distinct entity1) = 1;
Entity2
===========
444
16355

2 rows found[/tt]

 

[PHV]

Please, disregard my post as it is all wrong.

 

[stiley]

Thanks to both of you for your assistance.

Much appreciated
Sean