Eyal Eyal - 3 months ago 14
SQL Question

SQL Server : select when at least one mach from each option

I need to select from

Property
when at least 1 for each
Pid
is exists in
UserData


Property:

Pid OptId
----------
5 12
5 13
5 14
6 22
6 23
7 28
7 29


UserData:

UId Pid OptId
----------
1 5 13
1 6 23
1 7 28
2 5 12
2 6 22


The results should be:

UId Pid OptId
-------------
1 5 13
1 6 23
1 7 28


The results shows only the rows for
Uid=1
because it has at least one
Pid
for each property (5,6,7).
Uid=2
does not include in result because it has
pid = 5 & 6
but does not have
Pid=7


Is it possible?

Answer

SQL Demo

First you need to know how many properties are

 SELECT COUNT(DISTINCT Pid) as total_properties
 FROM Property

Then you need to see how many properties has each UserData

SELECT u.UId, count(DISTINCT u.Pid) as Uid_property
FROM UserData u
GROUP BY u.UId

Then join together

SELECT *
FROM UserData u
JOIN (
    SELECT u.UId, count(DISTINCT u.Pid) as Uid_property
    FROM UserData u
    GROUP BY u.UId
    ) t1
  ON u.UId = t1.UId
CROSS JOIN (
    SELECT COUNT(DISTINCT Pid) as total_properties
    FROM Property
   ) t2
WHERE t1.Uid_property = t2.total_properties

OUTPUT

enter image description here

Comments