CODE:
select
mpc_clnpsdep.code spsdep,
mpc_prprof.code sprof,
mpc_clnpersons.code spers,
companies.name scompany
from
mpc_clnpspfm
left join companies on companies.rn = mpc_clnpspfm.company
left join mpc_clnpsdep on mpc_clnpsdep.rn = mpc_clnpspfm.postrn
left join mpc_clnpersons on mpc_clnpersons.rn = mpc_clnpspfm.persrn
left join mpc_prprof on mpc_prprof.rn = mpc_clnpspfm.prprof
where mpc_clnpsdep.postrn in
(
select postrn /*, count(*)*/ from
(
select mpc_clnpsdep.postrn, mpc_clnpspfm.prprof
from mpc_clnpspfm, mpc_clnpsdep
where mpc_clnpsdep.rn = mpc_clnpspfm.postrn
--and mpc_clnpspfm.prprof is not null and mpc_clnpspfm.prprof <> 214705
group by mpc_clnpsdep.postrn, mpc_clnpspfm.prprof
)
group by postrn
having count(*) > 1
)
order by
mpc_clnpsdep.code,
mpc_prprof.code,
mpc_clnpersons.code,
companies.name
862 записи |