CODEWITHSUNDEEP
mysqlsql
David Shi
David Shi

Reputation: 69

MySQL query between three tables

I have three tables student, role, student-role(only has student id and role id). I want to return role(in role table) by checking the student name(in student table).

I tried: 

select role from role rr 
join student-role sr on sr.role-id=rr role-id 
join student ss on sr.student-id=ss.student-id
where ss.name="xxx"

My tables are:

Role
Role-id      | Role          |
-------------+---------------+
1            | admin         |
2            | whatever      |


Student
Student-id   | name          |
-------------+---------------+
1            | aaaaaaaa      |
2            | bbbbbbbb      |

Studnet-role
id           | Role-id       |student-id    |
-------------+---------------+--------------+
1            | 1             |2             |
2            | 2             |1             |

Can anyone tell me if this is correct?

Upvotes: 0

Views: 51

Answers (2)

Priyesh
Priyesh

Reputation: 541

I have modified column name and table to understand clearly.

create table role (role_id integer, role varchar(100));
create table std (std_id integer, name varchar(100));
create table std_role (id integer, role_id integer , std_id integer);


insert into role (role_id, role) values(1, "admin");
insert into role (role_id, role) values(2, "whatever");

insert into std (std_id, name) values(1, "aaaa");
insert into std (std_id, name) values(2, "bbbb");

insert into std_role (id,role_id,std_id) values(1,1,2);
insert into std_role (id,role_id,std_id) values(1,2,1);


SELECT role.* FROM std_role
INNER JOIN std ON (std.std_id=std_role.std_id)
INNER JOIN role ON (role.role_id=std_role.role_id)
WHERE std.name = 'aaaa';

Upvotes: 0

Martin
Martin

Reputation: 16433

Your query looks along the right lines.

I'd simply modify it to start with student and join to the other tables from there:

SELECT r.role
  FROM student s
    INNER JOIN `student-role` sr ON sr.`student-id` = s.`student-id`
    INNER JOIN role r ON r.`role-id` = sr.`role-id`
  WHERE (s.name = "xxx")

Of course, if a student has more than one role, you are going to return multiple rows with this query. You may not be expecting that.

Incidentally, I'd recommend against using hyphens (-) in your column names.

EDIT

I added backticks to the column names following @Strawberry's comment. Of course student-id is not a valid column name without backticks.

Upvotes: 2

Related Questions