Make single query with multiple tables in Mysql

Question

I have four tables as shown in the below:

user                    project               school                   coordinator
--------------          --------------        --------------           --------------
  user_id                 project_id             school_id             coordinator_id
  name                    project_name           school_name           project_id
  username                project_type           school_phone          user_id
  password                                       school_email
                                                 coordinator_id

Table Relationships

A project may have multiple coordinators
A Coordinator may have multiple schools
A user can be a coordinator of multiple projects.

Now I want to make a single query to get all the schools belonging to a single project. To make this query, I have a user_id of a project coordinator.

Currently I am doing it using few queries like below

01 -- Find project id/s for given user_id

SELECT project_id FROM coordinator WHERE user_id = 2;

+------------+
| project_id |
+------------+
|          1 |
|         14 |
+------------+

02. -- Find coordinator id/s for given project_id

SELECT coordinator_id 
  FROM coordinator c
  LEFT JOIN project p USING (project_id) 
WHERE c.project_id IN (1,14);

+----------------+
| coordinator_id |
+----------------+
|              2 |
|              8 |
|             12 |
|            175 |
|            181 |
+----------------+

03. -- Finaly get all the schools for given coordinator id/s

SELECT school_id FROM school WHERE coordinator_id IN (2,8,12,175,181);

Any comments and suggestions are greatly appreciated.

tandat · Accepted Answer · 2022-08-20 05:18:39Z

1

Some suggestions for improvement.

You can combine 01 and 02 to a single query like below

SELECT coordinator_id 
FROM coordinator
WHERE user_id = 2

You can use JOIN clause to link multiple tables and get necessary information

SELECT s.school_id
FROM coordinator c
LEFT JOIN school s
    ON c.coordinator_id = s.coordinator_id
WHERE c.user_id = 2

Moreover, JOIN is better than IN performance-wise.

Edit The above JOIN clause is still correct. But, because a coordinator can have multiple schools, and a school should only have a coordinator, I would prefer

SELECT s.school_id
FROM school s
LEFT JOIN coordinator c
    ON s.coordinator_id = c.coordinator_id
WHERE c.user_id = 2

edited Aug 20, 2022 at 5:18

answered Aug 20, 2022 at 3:35

tandat

2551 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ugsgknt Over a year ago

Thanks for your answer. But your 'query' gives schools for a coordinator not a project, where I want to get all the schools belonging to a project. A project may have multiple coordinators

tandat Over a year ago

Sorry for misunderstanding. Because your queries start with user_id, and I just rewrote it as a single query. You can just change the condition in the WHERE clause. For example: WHERE project_id = 1

Collectives™ on Stack Overflow

Make single query with multiple tables in Mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related