How to filter data by condition in the same row?

Question

SQL

Good day! I could not move away from the subject area, therefore I give a real example.
Tell me how to implement the condition:

There is a BIG query that displays tabular data from many tables, the cropped code is presented below.
Everything is fine, but it was necessary to add fields and make a condition: compare the fields if the
Name of the ancestor of the VG (auto-binding) = The name of the ancestor of the VG on the MPV (auto-binding) - we display the
Name of the ancestor of the VG (auto-binding) != The name of the ancestor of the VG on the MPV (auto-binding) and their more than two, then we output something that MATCHES
Name of the VG ancestor (auto-binding) != Name of the VG ancestor on the MPV (auto-binding) - DO NOT DISPLAY

Difficult to explain, it's more convenient to look at the picture. Red highlights what needs to be removed.

Still - MS SQL 2008 R2 is used, it will not be possible to raise the version to a higher one.

I think it's a JOIN, but I don't know how this is even possible to implement. As it should be - I give it in the

screenshot:

Simplified request code

use GMSN_XMAO
go

WITH myCTE (MPV_COD, [MPV_name], MPV_parent, MPV_NAME_PREDOK, Code_use_type, Code_mon_type, Code_district) AS
(
/* выборка потомков и предков из таблицы ВГ*/
SELECT t1.Code_MPV MPV_COD ,t1.[MPV_name] MPV_COD, t2.Code_MPV MPV_parent, t2.[MPV_name] MPV_NAME_PREDOK, t1.Code_use_type Code_use_type, t1.Code_mon_type Code_mon_type, t1.Code_district Code_district
FROM MPV_catalogue t1
LEFT JOIN MPV_catalogue t2 ON t1.Code_parent = t2.Code_MPV
),
CTE_WG ( Code_WG, WG_index, water_gorizont, Code_parent, WG_parent, parent_name ) AS
(
SELECT t1.Code_WG Code_WG, t1.WG_index WG_index, t1.[Water_gorizont] water_gorizont, t2.Code_WG parent_id, t2.WG_index WG_parent, t2.[Water_gorizont] parent_name
FROM Voc_water_gorizont t1
LEFT JOIN Voc_water_gorizont t2 ON t1.Code_parent = t2.Code_WG
--ТОЛЬКО локального уровня
-- where t1.Local = 1
),


CTE_MPV_WG ( Code_WG, WG_index, water_gorizont, Code_parent, WG_parent, parent_name ) AS
(
SELECT t1.Code_WG Code_WG, t1.WG_index WG_index, t1.[Water_gorizont] water_gorizont, t2.Code_WG parent_id, t2.WG_index WG_parent, t2.[Water_gorizont] parent_name
FROM Voc_water_gorizont t1
LEFT JOIN Voc_water_gorizont t2 ON t1.Code_parent = t2.Code_WG
)


SELECT

myCTE.MPV_COD as 'Служебный',
case when myCTE.Code_mon_type = '1'
then myCTE.MPV_COD
else myCTE.MPV_parent end as 'КОД МПВ',


CTE_MPV_WG.WG_parent as 'Индекс предка ВГ на МПВ (автопривязка)',
CTE_MPV_WG.parent_name as 'Наименование предка ВГ на МПВ (автопривязка)',
COALESCE(CTE_WG.WG_parent,'ПУСТО') as 'Индекс предка ВГ (автопривязка)', COALESCE(CTE_WG.parent_name,'ПУСТО') as 'Наименование предка ВГ (автопривязка)',
VZ_catalogue.Code_VZ


FROM [VZ_catalogue]


left join VZ_gidrogeol_feature
ON VZ_catalogue.Code_VZ = VZ_gidrogeol_feature.Code_VZ

left join
(
SELECT *
FROM Voc_water_gorizont
) WG_fed
ON VZ_gidrogeol_feature.Code_WG_fed = WG_fed.Code_WG

left outer join VZ_correspondence
ON VZ_catalogue.Code_VZ = VZ_correspondence.Code_VZ


left outer join myCTE
ON VZ_correspondence.Code_MPV = myCTE.MPV_COD

left JOIN
(

SELECT Region, Code_Region
FROM Voc_region
) MPV_REGION

ON myCTE.Code_district = MPV_REGION.Code_region



--ищем нужные ВГ в новой таблице
left outer join VZ_equipment t3
ON VZ_catalogue.Code_VZ = t3.Code_VZ


left outer join Voc_water_gorizont
ON t3.Code_WG = Voc_water_gorizont.Code_WG

left outer join CTE_WG
ON Voc_water_gorizont.Code_WG = CTE_WG.Code_WG

--ДОБАВЛЯЕМ ПРЕДКА ВГ НА МПВ
left join
MPV_Spares_WG
ON myCTE.MPV_COD = MPV_Spares_WG.Code_MPV
AND MPV_Spares_WG.Datasz IS NULL

left JOIN CTE_MPV_WG
ON MPV_Spares_WG.Code_WG = CTE_MPV_WG.Code_WG
AND CTE_WG.Code_WG = MPV_Spares_WG.Code_WG

--ДОБАВЛЯЕМ ПРЕДКА ВГ НА МПВ

where VZ_catalogue.Code_use_type IS NOT NULL


--- отладка дублей по ВГ
AND VZ_catalogue.Code_VZ = 711341003
--OR VZ_catalogue.Code_VZ = 711341003
--OR VZ_catalogue.Code_VZ = 711341004


--Группировка для вывода дополнительных столбцов по типам вод ХПВ, ПТВ и так далее

GROUP BY
myCTE.MPV_COD,
myCTE.Code_mon_type,
myCTE.MPV_parent,
myCTE.MPV_NAME_PREDOK,
myCTE.MPV_name,
VZ_catalogue.Code_VZ,

CTE_WG.WG_parent, CTE_WG.parent_name,
WG_fed.WG_index,
WG_fed.Water_gorizont

---Выводим предка ВГ НА МПВ

,CTE_MPV_WG.WG_parent
,CTE_MPV_WG.parent_name

Answer 1

'VG parent name as NP
'VG parent name on MPV (auto-binding) as NP_MPV

SELECT список_полей
GROUP BY список_полей
HAVING (COUNT(NP_MPV) = 1 AND NP = NP_MPV)
       OR (COUNT(NP_MPV) > 1 AND NP IS NOT NULL)