SQL query to find matches for multiple criteria

Question

If I had a PERMISSIONS table that 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 pure SQL query that would let me find out what PERSONs have access to what THINGs. Basically, I want a query that would look something like:

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

and have it return "John" and "Mary". The key point being the number of permissions necessary for access to the thing is arbitrary.

I feel like this should be obvious, but I just can't come up with an elegant solution. Oracle compatible solutions preferred.

Edit:

Solutions from Kosta and JBrooks work well. Below is a modified version of Kosta's solution that only hits the indexes twice, as opposed to 3x for Kosta's and 4x for JBrooks's (though I agree with JBrooks that this is probably unnecessary optimization).

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

I'm no Oracle expert, but I might be able to offer some handwaving assistance. For any given person, if "the distinct count of their permissions for a particular thing" equals "the distinct count of permissions required for that thing" then they can access that thing. If (PERSON, PERMISSION) and (THING, PERMISSION) are primary keys on the two tables then you can forget about the distinct aspect. — Will A, May 14 '11 at 00:44

JBrooks · Answer 1 · 2011-05-14T05:24:50.147

Ok, I get why people might use the Count() to match on and that could work, but I think that you will get into trouble doing this when things get a little more complicated (and they always get a little more complicated.)

If I say this problem in English it is:

Select the PEOPLE that have permission on that THING.
and there doesn't exist a PERMISSION required for that THING that the PERSON doesn't have.

So that SQL would be:

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

I'm a SQL Server guy now so the syntax might be a little off, but you get the idea.

Performance: Someone is going to say that this doesn't look like the most efficient SQL, so let me defend myself now. Permission and user tables are usually on the smaller side compared to the rest of a system and with DBMS today you would be hard pressed to load enough data in these tables to make this statement run longer than a tenth of a second - especially after indexes were in use. So in the past I would have agree about the performance - today I never worry about the performance unless it jumps out at me. So much less maintenance when you approach it this way.

This works well, but I had to read through it about three times to understand it. I think Kosta's solution is a little easier to understand (especially if I come back to this six months from now). — eaolson, May 14 '11 at 17:12

score 1 · Answer 2 · answered May 14 '11 at 03:00

1

Use:

  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 ensures that only values red and blue are included. The COUNT DISTINCT ensures that duplicates (two blue's) are not allowed, as that would be a false positive.

answered May 14 '11 at 03:00

OMG Ponies

325,700
82
523
502

I thought he was looking for something more generic - so it would work when there was a THING=pear / PERMISSION=yellow then Mary would have rights to pear. – JBrooks May 14 '11 at 05:22
@JBrooks: See the OPs comment to Nicholas Carey's answer – OMG Ponies May 14 '11 at 05:25

score 1 · Accepted Answer · answered May 14 '11 at 03:05

1

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')

answered May 14 '11 at 03:05

Kosta

518
3
7

Mary does't match this when she should. If there were a new permission of Black and Bob had it he would match when he shouldn't. – JBrooks May 14 '11 at 17:08
Judging by the execution plan, this is nearly equivalent to doing an INNER JOIN on the things table instead of the first subselect. – eaolson May 14 '11 at 17:09
@JBrooks: Actually, it does work for me and returns John and Mary. Also, giving Bob a new permission of "black", he's still not returned by this query. – eaolson May 14 '11 at 17:11
@eaolson: you are right, I misread it. Just not a big fan of matching on Count(). – JBrooks May 14 '11 at 17:16

score 0 · Answer 4 · answered May 14 '11 at 00:45

0

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

ought to do it.

answered May 14 '11 at 00:45

Nicholas Carey

71,308
16
93
135

my initial response was similar to the above, but I believe that both permissions for eggplant are required in order for a user to have access to it, not one-or-other-or-both. – Will A May 14 '11 at 00:47
Will A is correct. Your query, for example, would return Bob, who lacks the "blue" permission. – eaolson May 14 '11 at 02:33

SQL query to find matches for multiple criteria

4 Answers4

Linked