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:

roles table:

privileges table:

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.

  • I also think your ON clause should not have the same table (privileges) on both sides. Jul 13 '11 at 21:08

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
  ON p.role_id = r.id;
  • The privileges table is a join table joining the verbs and roles tables. It appears you are saying I need to do two nested inner joins to connect the relationship between the three tables? Jul 13 '11 at 20:56
  • Why "nested" inner joins? Let me take some liberties with column names and edit my answer. Jul 13 '11 at 21:03
  • verbs and roles dont relate to each other directly, they relate through the privileges table. verb has primary key. role has primery kay. privileges has two foreign keys, verb_id and role_id. Thats why I thought I needed a nested inner join because there is no direct relation between role and verb. Jul 13 '11 at 21:11
  • I can't follow the word problem. Can you show some sample data (rows in each of the three tables - just key and output columns will do) and what you expect returned from your query, given that sample data? Jul 13 '11 at 21:28
  • I updated question Jul 13 '11 at 21:56

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
  • I don't think so... "on p.verb_id = p.verb_id"? Jul 13 '11 at 21:43
  • Fixed the typo. It shoudld be "ON p.verb_id = v.verb_id". Although, looks like you have a revised answer as well. Kudos to you.
    – dabest1
    Jul 13 '11 at 23:51
  • Yeah, we finally figured out what the actual columns were named. Jul 14 '11 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.