CODEWITHSUNDEEP
mysqlsql
Jack Brown
Jack Brown

Reputation: 580

SQL: Multiple Joins and WHERE statements

I have a tabled named 'users' a table named 'modules' and a table named 'users_modules'.

'users' contains 'user_id', 'first_name', 'last_name' and 'role'.

'modules' contains 'module_code' and 'module_title'.

'users_modules' contains 'user_id' and 'module_code'

The 'role' column in the 'users' table specifys if that user is a 'student', 'lecturer' or 'admin'.

I want to display to the user, which modules they are studying including the module code, module title and also the names of the lecturers of this module.

I have managed to right the following statement which gives me the 'module_code' and 'module_title' for all the module's a certain user is studying.

SELECT users_modules.user_id,
       users_modules.module_code,
       modules.module_title
  FROM users_modules
 INNER JOIN modules
    ON users_modules.module_code = modules.module_code
 WHERE user_id = 2;

I now need to display which lecturers are enrolled for this module. Needs to be part of the above statement as I am using it within a PHP while loop to produce a HTML table. I understand I need to do another JOIN for the users table so I can get the names of the lecturers but not sure how to finish writing the code.

The code will have to check which users are enrolled for the module (using the users_module table) and then see if any of them users have a 'role' (column in the users table) which equals either 'Lecturer' or 'Admin'.

Overview of what I want:

A result which shows a student (in this example a student with user_id=2) what modules they are enrolled in and what lecturers teach this module.

For everyone that has an involvement within this module (either teacher or student) will have a record in 'users_modules'.

I need a way to pull out of the 'users_modules' table the lecturers for each module, this is done by viewing the 'role' column in the users table.

Upvotes: 1

Views: 125

Answers (3)

SS781
SS781

Reputation: 2649

Below should get you what you need, assuming your user table has a 'user_id' field. Also, note that it is always a good practice to alias your tables in your queries.

        SELECT u1.user_id AS 'Student ID', um1.module_code, m1.module_title,
        GROUP_CONCAT(u2.first_name) AS 'Lecturer First Name', 
        GROUP_CONCAT(u2.last_name) AS 'Lecturer Last Name'        
        FROM users u1 INNER JOIN users_modules um1 ON u1.user_id = um1.user_id
        INNER JOIN modules m1 ON um1.module_code = m1.module_code
        INNER JOIN users_modules um2 ON m1.module_code = um2.module_code
        INNER JOIN users u2 ON um2.user_id = u2.user_id
        WHERE u1.user_id = 2
        AND (u2.role = 'Lecturer' or u2.role = 'Admin')
        GROUP BY u1.user_id, um1.module_code, m1.module_title
        ;

Upvotes: 1

vp_arth
vp_arth

Reputation: 14982

Just add another join and necessary fields:

SELECT users_modules.user_id,
       users_modules.module_code,
       modules.module_title,
       users.first_name,
       users.last_name
  FROM users_modules
 INNER JOIN modules
    ON users_modules.module_code = modules.module_code
 INNER JOIN users
    ON user.user_id = users_modules.user_id
 WHERE users_modules.user_id = 2 AND users.role IN ('Lecturer', 'Admin');

Or with condition on join:

SELECT um.user_id,
       um.module_code,
       m.module_title,
       u.first_name,
       u.last_name
  FROM users_modules um
 INNER JOIN modules m
    ON um.module_code = m.module_code 
 INNER JOIN users u
    ON u.user_id = um.user_id
   AND u.role IN ('Lecturer', 'Admin')
 WHERE um.user_id = 2;

Upvotes: 0

sarin
sarin

Reputation: 5307

Quite simple. If your modules always have lecturers add another INNER JOIN statement under the last join (and ON). The join will be on the module_code. If some modules don't have lecturers then LEFT JOIN instead otherwise rows will be removed that you may want to see.

Upvotes: 0

Related Questions