CODEWITHSUNDEEP
mysqlsqlmultiple-tables
user1448731
user1448731

Reputation: 55

How to query multiple tables using a single query?

I want my tables to output something like this

---------------------------------------------------------------------------------------------
|  date         |  location | time  |        delegate 1         |       delegate 2          |
|--------------------------------------------------------------------------------------------
|  2015-12-07   |  Table 1  | 9:00  |  first_name_4 last_name_4 | first_name_5 last_name_5  |
|--------------------------------------------------------------------------------------------
|                           | 9:30  |  first_name_4 last_name_4 | first_name_6 last_name_6  |
|--------------------------------------------------------------------------------------------
|                           | 9:30  |  first_name_3 last_name_3 | first_name_7 last_name_7  |
|--------------------------------------------------------------------------------------------
|                           | 9:00  |  first_name_3 last_name_3 | first_name_7 last_name_7  |
|--------------------------------------------------------------------------------------------

Here are the tables on my db

meetings table

-------------------------------------------------------------------------------------------------
|  id   |  date_id  | time_id   | location_id   | delegate_id_1 |   delegate_id_2   | status    |
|------------------------------------------------------------------------------------------------
|   1   |     1     |     1     |       1       |       4       |         5         |     A     |
|------------------------------------------------------------------------------------------------
|   2   |     1     |     2     |       1       |       4       |         6         |     A     |
|------------------------------------------------------------------------------------------------
|   3   |     1     |     1     |       1       |       2       |         6         |     P     |
|------------------------------------------------------------------------------------------------
|   4   |     1     |     2     |       1       |       1       |         3         |     A     |
|------------------------------------------------------------------------------------------------
|   5   |     1     |     1     |       1       |       1       |         3         |     A     |
|------------------------------------------------------------------------------------------------

users table

-----------------------------------------
|  id   |  first_name   | last_name     | 
|----------------------------------------
|  1    |  first_name_1 | last_name_1   | 
|----------------------------------------
|  2    |  first_name_2 | last_name_2   | 
|----------------------------------------
|  3    |  first_name_3 | last_name_3   | 
|----------------------------------------
|  4    |  first_name_4 | last_name_4   | 
|----------------------------------------
|  5    |  first_name_5 | last_name_5   | 
|----------------------------------------
|  6    |  first_name_6 | last_name_6   | 
|----------------------------------------

locations table

-----------------------------
|  id   |  location_name    | 
|----------------------------
|  1    |  Table 1          | 
|----------------------------

time table

-------------------------
|  id   |  meeting_time | 
|------------------------
|  1    |  9:00:00      | 
|------------------------
|  1    |  9:30:00      | 
|------------------------

dates table

-------------------------
|  id   |  meeting_date | 
|------------------------
|  1    |  2015-12-07   | 
|------------------------
|  2    |  2015-12-08   | 
|------------------------
|  3    |  2015-12-09   | 
|------------------------

My initial query goes like this

-- $query_date
SELECT meeting_date 
FROM dates 
WHERE meeting_date = '2015-12-07'

-- $query_location
SELECT location_name.location 
from location 
LEFT JOIN meetings 
    ON meetings.location_id=location.id 
LEFT JOIN date 
    ON meetings.date_id=date.id 
WHERE meeting_date.dates = '2015-12-07'

Now, here's the part where I got it wrong.

-- $query_final
SELECT meeting_time.time, delegate1.first_name AS first_name_1,
   delegate1.last_name AS last_name_1, delegate2.first_name AS first_name_2,
   delegate2.last_name AS last_name_2 
FROM meetings 
INNER JOIN users delegate1 
    ON meetings.delegate_id_1=users.id 
LEFT JOIN users delegate2 
    ON meetings.delegate_id_2=users.id 
WHERE meetings.status='A'

The results on my last query give me unexpected results since the results show more entries than my meetings table.

I know the queries I made are costly but I don't know how to make a more optimized query. I don't even know if it's possible to get the results into a single query only. Any help well do. Thanks.

Upvotes: 1

Views: 77

Answers (1)

Blag
Blag

Reputation: 5894

You can bring back everything with a single query with the right JOIN.

Be Careful, when you use column name on SQL, the syntax is TABLE.COLUMN_NAME, it seem you mistake on the order quit often...

I changed some table name as you sometime use an s at the end and sometime no.

As time and date are SQL keyword, it's better with s everywhere

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE meetings  (`id` int, `date_id` int, `time_id` int, `location_id` int, `delegate_id_1` int, `delegate_id_2` int, `status` varchar(1));
INSERT INTO meetings (`id`, `date_id`, `time_id`, `location_id`, `delegate_id_1`, `delegate_id_2`, `status`)
VALUES (1, 1, 1, 1, 4, 5, 'A'),
    (2, 1, 2, 1, 4, 6, 'A'),
    (3, 1, 1, 1, 2, 6, 'P'),
    (4, 1, 2, 1, 1, 3, 'A'),
    (5, 1, 1, 1, 1, 3, 'A');

CREATE TABLE users (`id` int, `first_name` varchar(12), `last_name` varchar(11));
INSERT INTO users (`id`, `first_name`, `last_name`)
VALUES (1, 'first_name_1', 'last_name_1'),
    (2, 'first_name_2', 'last_name_2'),
    (3, 'first_name_3', 'last_name_3'),
    (4, 'first_name_4', 'last_name_4'),
    (5, 'first_name_5', 'last_name_5'),
    (6, 'first_name_6', 'last_name_6');

CREATE TABLE locations (`id` int, `location_name` varchar(7));
INSERT INTO locations (`id`, `location_name`)
VALUES (1, 'Table 1');

CREATE TABLE times (`id` int, `meeting_time` varchar(7));
INSERT INTO times (`id`, `meeting_time`)
VALUES  (1, '9:00:00'),
    (2, '9:30:00') ;

CREATE TABLE dates (`id` int, `meeting_date` varchar(10)) ;
INSERT INTO dates (`id`, `meeting_date`)
VALUES (1, '2015-12-07'),
    (2, '2015-12-08'),
    (3, '2015-12-09') ;

Query 1:

-- $query_final
SELECT locations.location_name,
   `times`.meeting_time, 
   delegate1.first_name AS first_name_1,
   delegate1.last_name AS last_name_1, 
   delegate2.first_name AS first_name_2,
   delegate2.last_name AS last_name_2 
FROM meetings 

LEFT JOIN locations 
    ON meetings.location_id=locations.id 
LEFT JOIN dates 
    ON meetings.date_id=`dates`.id 
LEFT JOIN times 
    ON meetings.time_id=`times`.id 
INNER JOIN users delegate1 
    ON meetings.delegate_id_1 = delegate1.id 
LEFT JOIN users delegate2 
    ON meetings.delegate_id_2 = delegate2.id 

WHERE 
    meetings.status = 'A'
    AND dates.meeting_date = '2015-12-07'

Results:

| location_name | meeting_time |   first_name |   last_name |   first_name |   last_name |
|---------------|--------------|--------------|-------------|--------------|-------------|
|       Table 1 |      9:00:00 | first_name_1 | last_name_1 | first_name_3 | last_name_3 |
|       Table 1 |      9:30:00 | first_name_1 | last_name_1 | first_name_3 | last_name_3 |
|       Table 1 |      9:00:00 | first_name_4 | last_name_4 | first_name_5 | last_name_5 |
|       Table 1 |      9:30:00 | first_name_4 | last_name_4 | first_name_6 | last_name_6 |

Upvotes: 1

Related Questions