12 - SQL Querying from multiple tables

Till now we queried only single table, in this chapter, we would learn about getting details from two or more tables. This can be done by using tables that have a relation between them (Like example Employee and Department, Order and Customer). This can be achieved by “JOIN”ing or doing “UNION” of tables. The concept of JOIN and UNION will be discussed further in coming chapters.

Syntax

SELECT col1,table1.col2, table2.col3,…colN

FROM table1, table2,…tableN

WHERE Field matching in table1, table2…tableN

Note: While mentioning the column names, table name can be prefixed with a dot, whenever same column names are present in multiple tables in the query.

Consider movie rental application, where there are 2 tables (Customer and movie transaction), suppose we have to get details of movies that the customers have seen.

1. Customer table

+-------------------+------------------+------------------+
| cust_id     | cust_name     | cust_area     |
+-------------------+------------------+------------------+
|       1     | James         | OH            |
|       2     | Ivy           | OH            |
|       3     | Linda         | PH            |
|       4     | Max           | PH            |
|       5     | Mike          | CL            |
+-------------------+------------------+------------------+

2. Movie_TRAN table.

+-------------------+---------------------------+------------------+------------------+
| movie_id  | moviename          | cust_id     | tran_date      |
+-------------------+---------------------------+------------------+------------------+
|      111  | LIFE IS BEAUTIFUL  |       2     | 2014-11-01     |
|      123  | GRAVITY            |       2     | 2014-12-01     |
|      134  | HELLBOY            |       4     | 2013-04-22     |
+-------------------+---------------------------+------------------+------------------+

3. Movie_details

+-------------------+----------------------------+---------------------------+------------------+
| movie_id  | moviename             | director             | year_release |
+-------------------+----------------------------+---------------------------+------------------+
|      111  | Life is beautiful     | Roberto Beningni     |         1997 |
|      123  | gravity               | Alfonso Cuaron       |         2013 |
|      134  | Hellboy               | Guillermo del Toro   |         2004 |
|      144  | Transformers          | Michael Bay          |         2007 |
+-------------------+----------------------------+---------------------------+------------------+


Get data from selected columns of 2 tables.  Get the Customer id, Name and Name of Movie that they have watched.

mysql> SELECT CUSTOMER.CUST_ID,CUST_NAME,MOVIENAME

    -> FROM CUSTOMER, MOVIE_TRAN

    -> WHERE CUSTOMER.CUST_ID = MOVIE_TRAN.CUST_ID;

This would give below result:

+-------------------+---------------------------+----------------------------+
| CUST_ID     | CUST_NAME     | MOVIENAME          |
+-------------------+---------------------------+----------------------------+
|       2     | Ivy           | LIFE IS BEAUTIFUL  |
|       2     | Ivy           | GRAVITY            |
|       4     | Max           | HELLBOY            |
+-------------------+---------------------------+----------------------------+
3 rows in set (0.02 sec)

Example 2:

Get data from selected columns of two or more tables.

Get the Customer Id, Name, Movie name and year of release.

mysql> SELECT CUSTOMER.CUST_ID,CUST_NAME,MOVIE_DETAILS.MOVIENAME,DIRECTOR

    -> FROM CUSTOMER, MOVIE_TRAN,MOVIE_DETAILS

    -> WHERE CUSTOMER.CUST_ID = MOVIE_TRAN.CUST_ID AND

    ->       MOVIE_TRAN.MOVIE_ID = MOVIE_DETAILS.MOVIE_ID;

 

This would give result as below:

+-------------------+-------------------+-------------------------+-----------------------------+
| CUST_ID     | CUST_NAME | MOVIENAME         | DIRECTOR             |
+-------------------+-------------------+-------------------------+-----------------------------+
|       2     | Ivy       | Life is beautiful | Roberto Beningni     |
|       2     | Ivy       | gravity           | Alfonso Cuaron       |
|       4     | Max       | Hellboy           | Guillermo del Toro   |
+-------------------+-------------------+-------------------------+-----------------------------+
3 rows in set (0.13 sec)

How this works is that first get the movie id for each customer id and then match the movie id from MOVIE TRAN table with MOVIE_DETAILS table to get corresponding director of the movie.

The same result can be achieved by using JOINS. The multiple tables can be merged using UNIONS. JOINS and UNIONS will be discussed further in coming chapters.

Like us on Facebook