Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I also think your ON clause should not have the same table (privileges) on both sides. –  Aaron Bertrand Jul 13 '11 at 21:08
add comment

2 Answers

up vote 2 down vote accepted

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;
share|improve this answer
    
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? –  JohnMerlino Jul 13 '11 at 20:56
    
Why "nested" inner joins? Let me take some liberties with column names and edit my answer. –  Aaron Bertrand 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. –  JohnMerlino 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? –  Aaron Bertrand Jul 13 '11 at 21:28
    
I updated question –  JohnMerlino Jul 13 '11 at 21:56
show 2 more comments

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
;
share|improve this answer
    
I don't think so... "on p.verb_id = p.verb_id"? –  Aaron Bertrand 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. –  Aaron Bertrand Jul 14 '11 at 0:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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