CODEWITHSUNDEEP
mysqlsqlselect
ugsgknt
ugsgknt

Reputation: 115

Make single query with multiple tables in Mysql

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

  1. A project may have multiple coordinators
  2. A Coordinator may have multiple schools
  3. 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.

Upvotes: 0

Views: 50

Answers (1)

tandat
tandat

Reputation: 255

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

Upvotes: 1

Related Questions