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.
Best Answer
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.: