mysql inner join producing more results than it should

Question

I am trying to get the roles name, verbs name, and privileges enabled value where privileges is a join table joining verbs and roles through a verb_id and roles_id foreign key. But I am having trouble with the join:

SELECT roles.name, verbs.name, privileges.enabled FROM verbs, roles 
INNER JOIN privileges on privileges.role_id = privileges.verb_id

It gives me way more records than there are. There are duplicate records for example many 'update student' records

verbs table:
id
name

roles table:
id 
name

privileges table:
id
role_id
verb_id
enabled

a verb has many roles through privileges a role has many verbs through privileges

I would like to show the role names, verb names, and privilege enabled value where the privileges role_id field is associated with the verb_id field. So for example, if role id 1 is associated with verb id 18, then the privileges table would have role_id 1 and verb_id 18 to show the relationship between role and verb. So I would like to select that relationship to display the associating role name, verb name, and privilege enabled value.

Answer 1

Why are you using two syntaxes, old fashioned verbs, roles and then an explicit inner join? Makes the query very hard to read and since you don't have any where clause that defines the relationship between verbs and roles (this is one of the biggest problems with using old fashioned syntax), you essentially have the cartesian product of those first two tables, and this probably explains your row counts.

You need to define the join columns between verbs and roles and, preferably, put that into an ON clause with a proper INNER JOIN. E.g.:

SELECT r.name, v.name, p.enabled
FROM verbs AS v
INNER JOIN privileges AS p
  ON v.id = p.verb_id
INNER JOIN roles AS r
  ON p.role_id = r.id;

Answer 2

Based on Aaron Bertrand's answer and the comments, I think this is what you are looking for:

SELECT r.name, v.name, p.enabled
FROM privileges AS p
INNER JOIN roles AS r ON p.role_id = r.role_id
INNER JOIN verbs AS v ON p.verb_id = v.verb_id
;