Geek asks and answers

SQL query to match multiple criteria

If I had a table PERMISSIONSthat looked like this:

PERSON         PERMISSION
------         ----------
Bob            red
John           red
John           blue
Mary           red
Mary           blue
Mary           yellow

and a THINGS table that looks like this:

THING          PERMISSION
-----          ----------
apple          red
eggplant       red
eggplant       blue

I'm trying to come up with a clean SQL query that will let me know that I PERSONhave access to those THINGs. Basically, I want a query that will look something like this:

SELECT person
  FROM ... vague handwaving here ...
 WHERE thing = 'eggplant'

and return it to John and Mary. The key point is the number of permissions required to access the item, arbitrarily.

I feel this should be obvious, but I just can't come up with an elegant solution. It is recommended that you use Oracle compatible solutions.

Edit:

Kosta JBrooks . Kosta, , 3x Kosta 4x JBrooks ( JBrooks , , , ).

SELECT p.person, num_permission, COUNT(p.person)
FROM permissions p
INNER JOIN (
    SELECT permission,
           COUNT(1) OVER (PARTITION BY thing) AS num_permission
      FROM things
     WHERE thing = 'eggplant'
  ) t ON t.permission = p.permission 
GROUP BY p.person, num_permission
HAVING COUNT(p.person) = num_permission
+3
4
select person
from permissions 
where permission in (select permission from things where thing='eggplant')
group by person
having count(person) = (select count(permission)  from things where thing='eggplant')
+1

, , Count() , , , , ( .)

, :

  • , THING.
  • , PERSON .

SQL :

SELECT DISTINCT P.PERSON, T.THING
FROM PERMISSIONS P
INNER JOIN THINGS T
ON P.PERMISSION = T.PERMISSION
WHERE NOT EXISTS
    (SELECT 1
    FROM THINGS TSUB
    WHERE TSUB.THING = T.THING
    AND TSUB.PERMISSION NOT IN
        (SELECT PSUB.PERMISSION
        FROM PERMISSIONS PSUB
        WHERE PSUB.PERSON = P.PERSON))
ORDER BY P.PERSON, T.THING

SQL Server, , .

: - , SQL, . , - , . - , . , .

+2

:

  SELECT p.person
    FROM PERMISSIONS p
    JOIN THINGS t ON t.permission = p.permission
   WHERE t.permission IN ('red', 'blue')
GROUP BY p.person
  HAVING COUNT(DISTINCT t.permission) = 2

The WHERE clause only includes red and blue values. COUNT DISTINCTensures that duplicates (two blue) are not allowed, as this will be false positive.

+1
source 
select distinct person
from permissions p
join things      t on t.permission = p.permission
                  and t.thing      = 'eggplant'

must do it.

0
source

More articles:


All Articles