How To Find More Than One Record?

[VAST39]

Hi everyone,

I'm a bit stumped on this one, but I think I'm on the right track. I need to run a query that finds employees who are currently have more than one employer. The employer is referenced by an agency number.


Sample Table looks like this:

EMPR TABLE

SSN AGENCY LAST EMPLOYED DATE

123456789 4533 NULL
123456789 2345 NULL


So this sample shows a person who is working in two different agencies. A NULL date means it is an active employment.


How do I write a query to look for only SSNS that meet this condition? I've done this before years ago, and I believe I used a self-join, but I'm not 100% sure if this is right:

select * from empr a,
              empr b
              
 where a.ssn = b.snn
 and a.agency <> b.agency
 and LAST EMPLOYED DATE is NULL

Am I on the right track?

 

[imex]

Hi,

Try:

with CTE as
(
    select ssn
    from empr
    group by ssn
    having count(distinct agency) > 1
)

select * 
from CTE as c
join empr as e on e.ssn = c.ssn

Hope it's useful.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[imex]

Forgot the condition:

with CTE as
(
    select ssn
    from empr
    where [LAST EMPLOYED DATE] is NULL 
    group by ssn
    having count(distinct agency) > 1
)

select * 
from CTE as c
join empr as e on e.ssn = c.ssn

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]