Search code examples
sqlcds

How to not get data if a field is found more than once

I'm getting results from a Query of installations and equipment number, however if I have more than one equipment in an installation I want to omit that data.

select a~anlage, c~equnr
from eastl as a 
inner join egerh as b on a~logiknr = b~logiknr and a~bis = '99991231' and b~bis = '99991231'
inner join equi as c on b~equnr = c~equnr and c~matnr = 'REGULATOR'
group by a~anlage, c~equnr 
having count(*) = 1

My current results looks like this:

anlage - equnr
111111 - 01012
111111 - 01013
111111 - 01014
222222 - 01015
222222 - 01016
444444 - 01017

And I expect this:

anlage - equnr
444444 - 01017
Solution

  • It seems that you need to group by a~anlage only: 

    select a~anlage, max(c~equnr)
from eastl as a 
inner join egerh as b on a~logiknr = b~logiknr and a~bis = '99991231' and b~bis = '99991231'
inner join equi as c on b~equnr = c~equnr and c~matnr = 'REGULATOR'
group by a~anlage
having count(*) = 1