CODEWITHSUNDEEP
mysqlsql
Josh
Josh

Reputation: 702

SQL query with JOIN, WHERE, GROUPBY, COUNT

Below is an example of the schema I am working with.

How can I return the id and name of all users who have a permission, but DO NOT have a matching certification for that permission?

For example, the query would return 0, john in this case, since john has the "delete" permission, but does not have a matching "delete" certification.

(This schema is arbitrary and contrived, I'm just trying to get at the syntax/select logic for this query)

users
=====
id  name
--------
0   john
1   joe

user_permissions
================
uid permission
--------------
0   'edit'
0   'delete'
1   'edit

user_certs
==========
uid cid
-------
0   'edit'
1   'edit'

I've tried, this, and I know the last line is wrong.

SELECT DISTINCT id, name FROM users
LEFT JOIN user_permissions users ON users.uid = user_permissions.uid
LEFT JOIN user_certs ON users.id = user_certs.uid
WHERE (user.permission = 'delete')
GROUP BY id, name
HAVING (COUNT(user_certs.cid = 'delete') = 0)

Upvotes: 0

Views: 62

Answers (2)

cHillb3rT
cHillb3rT

Reputation: 9

Bud you are right.

The alias user dies not exist.

Upvotes: 0

Guffa
Guffa

Reputation: 700690

Get all the permissions that doesn't have a matching certificate, then group on the user:

select
  u.id,
  u.name
from
  users u
  inner join user_permissions p on p.uid = u.id
  left join user_certs c on c.uid = p.uid and c.cid = p.permission
where
  c.uid is null
group by
  u.id,
  u.name

Upvotes: 2

Related Questions