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.