T-sql at least one common record condition
I have 2 tables Regions
and RegionNodes
and am trying to find the correct t-sql query.
Regions
:
Id| Name
--+---------------
-1| Global
0 | North America
1 | South America
2 | Asia
3 | Pacific
4 | Africa
Another table RegionNodes
Id | NodeId | RegionId
---+--------+---------
1 | 1 | -1
2 | 1 | 0
3 | 2 | -1
4 | 2 | 1
5 | 3 | -1
6 | 3 | 2
7 | 4 | -1
8 | 5 | -1
In RegionNodes
all entries must have one entry RegionId = -1
and one or more other areas. The goal is for the node to have a global AND different region. So at the top RegionNodes
the table IDs 1,2,3,4,5,6 are correct, but 7 and 8 are not; as they only have a global region and not others.
How can I find records like this that only have global regions but not any other region so that I can clear the data and put a condition to be validated? Please help. I really appreciate the help of the experts.
Probably the easiest way in your case is to just find the nodes that appear once:
select NodeId
from RegionNodes rn
group by NodeId
having count(*) < 2;
You can be more specific with a more refined sentence having
:
select NodeId
from RegionNodes rn
group by NodeId
having sum(case when RegionId = -1 then 1 else 0 end) <> 1 or
sum(case when RegionId <> -1 then 1 else 0 end) = 0;
This returns nodes that do not have one global and one non-global scope.
This should work:
select NodeId from RegionNodes
group by NodeId
having not(min(RegionId) = -1 and max(RegionId) >=0)
SELECT NodeId
FROM RegionNodes
WHERE RegionId= -1 AND NodeId NOT IN (SELECT NodeId
FROM RegionNodes
WHERE RegionId <> -1 )
The above solutions will work if you just want to return nodeIds for nodes that only have a global entry. If you want to return a complete record for these records (id, nodeId and regionId), then an alternative solution would be the following:
select n.*
from dbo.RegionNodes as n
where n.regionId=-1
and not exists
(
select 1
from dbo.RegionNodes as n2
where n2.nodeId=n.nodeId
and n2.regionId<>n.regionId
)