SQL query, How to get entities not used in at least one input group or not used in any groups?

Question

Let me elaborate with examples.

I have three tables Entity(EntityId int), Group(GroupId int), and Mapping(MapId int, GroupId int, EntityId int).

Let's take a few rows of data.

Entity
EntityId
1
2
3
4

Group
GroupId
101
102
103

Mapping
MapId GroupId EntityId
1       101      2
2       102      1
3       102      3
4       103      3

I need to select entities that are not used in at least one input group or are not used in any non-input groups.

Input-GroupId Output-Entities
   101          1,3,4 ( 2 is mapped with 101)
   101,102      1,2,3,4( 1 is mapped with 102 but not with 101 and 2 is mapped with 101 but not with 102)
   102,103      1,2,4 ( 3 is mapped with both 102 and 103 so it will not be in output.)

If there are two input groups. According to the below ven diagram, I need values of all cross-red line areas.

I tried a few join queries but I am able to get only values that are not used in any groups.

Edit 1:-

SELECT distinct EntityId from Entity e
JOIN Mapping map ON e.EntityId != map.EntityId 
JOIN #temp t ON map.GroupId = t.GroupId  order by EntityId

I have tried this query. It works fine for Input-Groups- (101), (101, 102) but does not work for the case (102, 103).

Edit 2:- A few comments mentioned that the ven diagram is not clear. So this edit.

Let's consider, A(101) and B(102), two groups are provided as input. then all red dotted areas will be considered for the output. The blue pen highlighted area will not be considered.

Please explain what the Venn diagram areas contain & what A & B label. Dumping a picture that has some vague connection with "not in" is not helpful. LIkely there is a relevant Venn, but this one isn't yet, and it need to involve tables with the same columns & no duplicate rows. PS [Re abuse of such Venn-like diagrams.](https://stackoverflow.com/a/55642928/3404097) — philipxy, May 12 '23 at 20:41
[SQL - find records from one table which don't exist in another](https://stackoverflow.com/q/367863/3404097) — philipxy, May 12 '23 at 21:59
For example, ```101 1,3,4 ( 2 is mapped with 101)``` 101 is an input group.102 and 103 are non-input groups. So I need to fetch entities that are not used in 101 and used in 102,103. @nbk This is an example with a single input group. — HungryWolf, May 13 '23 at 04:06
@philipxy, I have attached ven diagram to explain mathematical/logical relations in the case of two groups as input. Please consider the example, ``` 101,102 1,2,3,4( 1 is mapped with 102 but not with 101 and 2 is mapped with 101 but not with 102)```. A(101) and B(102). A is linked with Entity 2 and B is linked with Entity 1. Other remaining entities are either linked with other groups(non-input groups) or not linked. 1 or 2 both are not linked with A and B so they will be considered for the output. Also, other entities do not have any linking with A or B so they will be considered too — HungryWolf, May 13 '23 at 04:16
Please clarify via edits, not comments. But your comment is unintelligible. Including what A & B are supposed to be & what you put in triple quotes, including what "is mapped with" is trying to say here or what is mapped to what, or what you are trying to say by "linked with" or what "be considered" is trying to say. Again: What are A & B? They need to be sets & if they are sets of (sub)rows they need to have the same columns. And what does the indicated difference set have to do with the query result? Anyway, the diagram says no more than the text A - B, or A EXCEPT B. It's just noise. — philipxy, May 13 '23 at 05:53
Presumably A & B are "are two input groups", but you don't connect a given A & B pair or the diagram to the base tables, the desired query, a result or the rest of the post. — philipxy, May 13 '23 at 06:01

score 0 · Answer 1 · answered May 12 '23 at 17:21

0

You can use

But i am still unsure about the not input groups

SELECT 
  EntityId
FROM Entity WHERE EntityId NOT IN (SELECT DISTINCT EntityId FROM Mapping )

EntityId
4

SELECT 
  EntityId
FROM Entity e WHERE NOT EXISTS (SELECT 1 FROM Mapping m WHERE m.EntityId = e.EntityId)

EntityId
4

fiddle

answered May 12 '23 at 17:21

nbk

45,398
8
30
47

Your query gives the entities that are not mapped to any group. – HungryWolf May 13 '23 at 04:42
What you seems to want that automatically all permutation are also calculated, bur math will show you that you have now 6 possibilities , with 4 our you have 24 and so on. Another point usy that you need programming not not SQL for this so that only with a stored procedure you could solve this. So you must think about what do I want – nbk May 13 '23 at 06:41
With a loop SP? – HungryWolf May 13 '23 at 12:08
see https://stackoverflow.com/questions/3621494/the-most-elegant-way-to-generate-permutations-in-sql-server – nbk May 13 '23 at 12:14

GMB · Accepted Answer · 2023-05-13T21:08:08.440

0

For a fixed list of groups given as input, you want entities that do not belong to all groups.

That’s a variant of the relational division problem. I would recommend passing the list of groups as rows using values(), and combining it with the entities table. Then we can bring the mapping table with a left join, and use the having clause to filter on the count of matches.

with p as (select * from ( values (101), (102) ) p(groupId) )
select e.entityId
from p
cross join entity e
left join mapping m
    on  m.entityId = e.entityId
    and m.groupId  = p.groupId
group by e.entityId
having count(m.entityId) <> ( select count(*) from p)

As an alternative for input, you could have the query accept a comma-separated list of values (hence as a single string parameter), and split it to rows with string_split - if that’s easier for the client application to manage.

Here is a demo on DB Fiddle, which yields the expected results for the three test cases of your question:

Input (GroupId)   Output (EntityId)
101               1,3,4
101,102           1,2,3,4
102,103           1,2,4

edited May 13 '23 at 21:08

answered May 12 '23 at 20:17

GMB

216,147
25
84
135

1st, Entities that do not belong to all input groups, are linked to other groups or not linked at all. 2nd, I tried your query it says "Invalid object name 'p'.", Am I missing something? Thanks. – HungryWolf May 13 '23 at 04:38
Now, I have also added a SQL query to my question. I have used it but did not get the desired output. – HungryWolf May 13 '23 at 04:57
@HungryWolf: *Invalid object name 'p'.*: my bad, there was a glitch in the query. I fixed it and added a fiddle to my answer. This works as expected, as far as I see. – GMB May 13 '23 at 21:09
Your answer is correct. So I am accepting it. I was also able to do the same with left join. ```select e.entityId from Entity e left join mapping m on m.entityId = e.entityId LEFT JOIN #TempGroupIds temp ON m.GroupId = temp.GroupId group by e.entityId having count(m.entityId) <> (select count(*) from #TempGroupIds) ``` – HungryWolf May 15 '23 at 06:59

SQL query, How to get entities not used in at least one input group or not used in any groups?

2 Answers2