MYSQL JOIN 3 TABLES

Mysql Join 3 Tables is used to join 3 Tables using left join. The left join returns you only selective records which are common in tables on the basis of common column.

Understand with Example

The Tutorial illustrate an example from 'Mysql Join 3 Tables' using Left Join. To grasp this example we create a table 'roseindia'. The create table construct a table 'roseindia' with fieldname and datatype.

Query to Create Table named roseindia:-

mysql> CREATE TABLE roseindia (              
            -> Empid int(11),         
             ->firstname varchar(30), 
            -> city varchar(30)       
          -> );
Query OK, 0 rows affected (0.05 sec)

Query to insert data into Table named roseindia:-

The insertinto add the records or rows to the table 'roseindia'.

mysql>  insert into roseindia values(01,'Girish','Nainital');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into roseindia values(02,'Komal','Merrut');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into roseindia values(03,'Amit','Lucknow');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into roseindia values(04,'Sandeep','Lucknow');
Query OK, 1 row affected (0.03 sec)

Query to view data of  Table named roseindia:-

mysql> select * from roseindia;

Output:-

+-------+-----------+----------+
| Empid | firstname | city     |
+-------+-----------+----------+
|     1 | Girish    | Nainital |
|     2 | Komal     | Merrut   |
|     3 | Amit      | Lucknow  |
|     4 | Sandeep   | Lucknow  |
+-------+-----------+----------+
4 rows in set (0.00 sec)

Query to Create Table newstrack:-

We create another table 'newstrack' with required field and data type respectively.

mysql> CREATE TABLE newstrack (              
        ->     Empid int(11),         
         ->    firstname varchar(10), 
         ->    email varchar(30)
          -> );
Query OK, 0 rows affected (0.03 sec)

Query to insert data into Table named newstrack:-

The insert into add the records or rows into a table newstrack.

mysql>  insert into newstrack values(01,'Suman','girish@gmail.com');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into newstrack values(02,'Ravi','komal@gmail.com');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into newstrack values(03,'Santosh','Amit@gmail.com');
Query OK, 1 row affected (0.01 sec)

Query to view data of  Table named newstrack:-

mysql> select * from newstrack;

Output:-

+-------+-----------+------------------+
| Empid | firstname | email            |
+-------+-----------+------------------+
|     1 | Suman     | girish.gmail.com |
|     2 | Ravi      | komal.gmail.com  |
|     3 | Santosh   | Amit.gmail.com   |
+-------+-----------+------------------+
3 rows in set (0.00 sec)

Query to Create Table named employee:-

We create another table 'employee' with fieldname and datatype respectively.

mysql> CREATE TABLE employee (                
             ->Empid int(11),          
             ->first_name varchar(30), 
             ->last_name varchar(15),  
             ->start_date date,        
             ->end_date date,          
             ->city varchar(10),       
             ->description varchar(15)
          );
Query OK, 0 rows affected (0.05 sec)

Query to insert data into Table named employee:-

The insert into add the records or rows to the  table 'employee'.

mysql>  insert into employee values(01,'
Girish','Tewari','2006-12-31','2010-06-25','Nainital','Programmer');
Query OK, 1 row affected (0.02 sec)

mysql> insert into employee values(02,'
Komal','Choudhry','2006-12-31','2010-04-21','Meerut','Programmer');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into employee values(03,'
Mahendra','Singh','2006-12-31','2007-05-12','Lucknow','Programmer');
Query OK, 1 row affected (0.02 sec)

Query to view data of  Table named employee:-

mysql> select * from employee;

Output:-

+-------+------------+-----------+------------+------------+----------+-------------+
| Empid | first_name | last_name | start_date | end_date   | city     | description |
+-------+------------+-----------+------------+------------+----------+-------------+
|     1 | Girish     | Tewari    | 2006-12-31 | 2010-06-25 | Nainital | Programmer  |
|     2 | Komal      | Choudhry  | 2006-12-31 | 2010-04-21 | Meerut   | Programmer  |
|     3 | Mahendra   | Singh     | 2006-12-31 | 2007-05-12 | Lucknow  | Programmer  |
+-------+------------+-----------+------------+------------+----------+-------------+
3 rows in set (0.00 sec)

Query to join data of  above 3 Tables created above:-

The below Query merge three tables that return you only those selective records present in tables on the basis of common column in the tables.

SELECT * FROM employee
LEFT JOIN roseindia ON employee.Empid = roseindia.empid
LEFT JOIN newstrack ON employee.Empid = newstrack.empid;

Output:-

+-------+------------+-----------+------------+------------+----------+-------------+-
| Empid | first_name | last_name | start_date | end_date   | city     | description |
+-------+------------+-----------+------------+------------+----------+-------------+-
|     1 | Girish     | Tewari    | 2006-12-31 | 2010-06-25 | Nainital | Programmer  |
|     2 | Komal      | Choudhry  | 2006-12-31 | 2010-04-21 | Meerut   | Programmer  |
|     3 | Mahendra   | Singh     | 2006-12-31 | 2007-05-12 | Lucknow  | Programmer  |
+-------+------------+-----------+------------+------------+----------+-------------+-

+-------+-----------+----------+-------+-----------+------------------+
| Empid | firstname | city     | Empid | firstname | email            |
+-------+-----------+----------+-------+-----------+------------------+
|     1 | Girish    | Nainital |     1 | Suman     | girish.gmail.com |
|     2 | Komal     | Merrut   |     2 | Ravi      | komal.gmail.com  |
|     3 | Amit      | Lucknow  |     3 | Santosh   | Amit.gmail.com   |
+-------+-----------+----------+-------+-----------+------------------+
3 rows in set (0.00 sec)

Thx u for sumber tutorial http://www.roseindia.net/sql/sqljoin/mysql-join-3-tables.shtml

LEFT JOIN,INNER JOIN,RIGHT JOIN

LEFT JOIN adalah relasi antar tabel, namun query yang ditampilkan adalah mengacu pada tabel yang sebelah kiri / tabel utama.
misal seperti ini :

mysql> select * from mahasiswa order by id asc;
+----+--------------+----------+-----------------------+----------+
| id | nim | kota | fakultas | angkatan |
+----+--------------+----------+-----------------------+----------+
| 1 | A10.111.1031 | Semarang | Manajemen Informatika | 2010 |
| 2 | A10.111.1032 | Semarang | Teknik Informatika | 2010 |
| 3 | A11.111.1040 | Kudus | Manajemen Informatika | 2009 |
| 4 | A11.111.1041 | Salatiga | Sistem Informasi | 2009 |
| 5 | A11.111.1043 | Demak | Teknik Informatika | 2010 |
+----+--------------+----------+-----------------------+----------+
5 rows in set (0.05 sec)

mysql> select * from nim order by id asc;
+----+--------------+----------+
| id | nim | nama |
+----+--------------+----------+
| 1 | A10.111.1031 | Musthofa |
| 2 | A10.111.1032 | Bisri |
| 3 | A11.111.1040 | Alex |
| 4 | A11.111.1041 | Graham |
+----+--------------+----------+
4 rows in set (0.00 sec)

Jika kedua tabel itu kita relasi dengan LEFT JOIN maka akan menjadi seperti ini :

mysql> select a.id, a.nim, a.kota, a.fakultas, a.angkatan, b.nama from mahasiswa a left join nim b on a.nim=b.nim;
+----+--------------+----------+-----------------------+----------+----------+
| id | nim | kota | fakultas | angkatan | nama |
+----+--------------+----------+-----------------------+----------+----------+
| 1 | A10.111.1031 | Semarang | Manajemen Informatika | 2010 | Musthofa |
| 2 | A10.111.1032 | Semarang | Teknik Informatika | 2010 | Bisri |
| 3 | A11.111.1040 | Kudus | Manajemen Informatika | 2009 | Alex |
| 4 | A11.111.1041 | Salatiga | Sistem Informasi | 2009 | Graham |
| 5 | A11.111.1043 | Demak | Teknik Informatika | 2010 | NULL |
+----+--------------+----------+-----------------------+----------+----------+
5 rows in set (0.00 sec)

nah terlihat total data yang ditampilkan ada 5 baris, namun lihat pada baris kelima kolom nama, disitu tertulis NULL. kenapa NULL ? yach karena data tidak ditemukan di tabel nim.

Nah, jika INNER JOIN query yang ditampilkan adalah data yang sama antara tabel pertama dengan tabel kedua, misalnya kayak gini :

mysql> select a.id, a.nim, a.kota, a.fakultas, a.angkatan, b.nama from mahasiswa a right join nim b on a.nim=b.nim;
+------+--------------+----------+-----------------------+----------+----------+
| id | nim | kota | fakultas | angkatan | nama |
+------+--------------+----------+-----------------------+----------+----------+
| 1 | A10.111.1031 | Semarang | Manajemen Informatika | 2010 | Musthofa |
| 2 | A10.111.1032 | Semarang | Teknik Informatika | 2010 | Bisri |
| 3 | A11.111.1040 | Kudus | Manajemen Informatika | 2009 | Alex |
| 4 | A11.111.1041 | Salatiga | Sistem Informasi | 2009 | Graham |
+------+--------------+----------+-----------------------+----------+----------+
4 rows in set (0.00 sec)

Klo yang ini, data yang ditampilkan adalah data yang sama/data yang ada antara tabel mahasiswa dan tabel nim.

Nah yang terakhir RIGHT JOIN, kebalikan dari LEFT JOIN tabel yang digunakan sebagai keynya adalah tabel kedua. contohnya kayak gini :

mysql> select a.id, a.nim, a.kota, a.fakultas, a.angkatan, b.nama from mahasiswa a right join nim b on a.nim=b.nim;
+------+--------------+----------+-----------------------+----------+----------+
| id | nim | kota | fakultas | angkatan | nama |
+------+--------------+----------+-----------------------+----------+----------+
| 1 | A10.111.1031 | Semarang | Manajemen Informatika | 2010 | Musthofa |
| 2 | A10.111.1032 | Semarang | Teknik Informatika | 2010 | Bisri |
| 3 | A11.111.1040 | Kudus | Manajemen Informatika | 2009 | Alex |
| 4 | A11.111.1041 | Salatiga | Sistem Informasi | 2009 | Graham |
+------+--------------+----------+-----------------------+----------+----------+
4 rows in set (0.00 sec)

ini hampir sama dengan INNER JOIN, namun jika pada tabel nim saya tambahkan lagi, misal tabel nim menjadi seperti ini:

mysql> select * from nim order by id asc;
+----+--------------+----------+
| id | nim | nama |
+----+--------------+----------+
| 1 | A10.111.1031 | Musthofa |
| 2 | A10.111.1032 | Bisri |
| 3 | A11.111.1040 | Alex |
| 4 | A11.111.1041 | Graham |
| 5 | A20.111.1111 | THofu |
| 6 | A20.111.1112 | Zida |
+----+--------------+----------+
6 rows in set (0.00 sec)

mysql>

dan jika kita query lagi dengan RIGHT JOIN, maka bisa liat sendiri terdapat perbedaan kan.

mysql> select a.id, a.nim, a.kota, a.fakultas, a.angkatan, b.nama from mahasiswa a right join nim b on a.nim=b.nim;
+------+--------------+----------+-----------------------+----------+----------+
| id | nim | kota | fakultas | angkatan | nama |
+------+--------------+----------+-----------------------+----------+----------+
| 1 | A10.111.1031 | Semarang | Manajemen Informatika | 2010 | Musthofa |
| 2 | A10.111.1032 | Semarang | Teknik Informatika | 2010 | Bisri |
| 3 | A11.111.1040 | Kudus | Manajemen Informatika | 2009 | Alex |
| 4 | A11.111.1041 | Salatiga | Sistem Informasi | 2009 | Graham |
| NULL | NULL | NULL | NULL | NULL | THofu |
| NULL | NULL | NULL | NULL | NULL | Zida |
+------+--------------+----------+-----------------------+----------+----------+
6 rows in set (0.01 sec)

sumber: http://shfind.blogspot.com/2010/08/penggunaan-left-inner-dan-right-join.html

Calculate Hour and Time in Mysql

To calculate an interval between two dates or times, either use one of the temporal-difference functions, or convert your values to basic units and take the difference. The allowable functions depend on the types of the values for which you want to know the interval.The following discussion shows several ways to perform interval calculations.

Calculating intervals with temporal-difference functions

To calculate an interval in days between two date values, use the DATEDIFF() function:

mysql>SET @d1 = '2010-01-01', @d2 = '2009-12-01';

mysql> SELECT DATEDIFF(@d1,@d2) AS 'd1 - d2', DATEDIFF(@d2,@d1) AS 'd2 - d1';

+---------+---------+

| d1 - d2 | d2 - d1 |

+---------+---------+

|      31 |     -31 |

+---------+---------+

DATEDIFF() also works with date-and-time values, but it ignores the time part. This makes it suitable for producing day intervals for DATE, DATETIME, or TIMESTAMP values.
To calculate an interval between TIME values as another TIME value, use the TIMEDIFF() function:

mysql>SET @t1 = '12:00:00', @t2 = '16:30:00';

mysql> SELECT TIMEDIFF(@t1,@t2) AS 't1 - t2', TIMEDIFF(@t2,@t1) AS 't2 - t1';

+-----------+----------+

| t1 - t2   | t2 - t1  |

+-----------+----------+

| -04:30:00 | 04:30:00 |

+-----------+----------+

TIMEDIFF() also works for date-and-time values. That's it, it accepts either time or date-and-time values, but the types of the arguments must match.
A time interval expressed as a TIME value can be broken down into components. For example, to express a time interval in terms of its constituent hours, minutes, and seconds values, calculate time interval subparts in SQL using the HOUR(), MINUTE(), and SECOND() functions. (Don’t forget that if your intervals may be negative, you need to take that into account.) To determine the components of the interval between the t1 and t2 columns in the time_val table, the following SQL statement does the trick:

mysql>SELECT t1, t2,

    -> TIMEDIFF(t2,t1) AS 't2 - t1 as TIME',

    -> IF(TIMEDIFF(t2,t1) >= 0,'+','-') AS sign,

    -> HOUR(TIMEDIFF(t2,t1)) AS hour,

    -> MINUTE(TIMEDIFF(t2,t1)) AS minute,

    -> SECOND(TIMEDIFF(t2,t1)) AS second

    -> FROM time_val;

+----------+----------+-----------------+------+------+--------+--------+

| t1       | t2       | t2 - t1 as TIME | sign | hour | minute | second |

+----------+----------+-----------------+------+------+--------+--------+

| 15:00:00 | 15:00:00 | 00:00:00        | +    |    0 |      0 |      0 |

| 05:01:30 | 02:30:20 | -02:31:10       | -    |    2 |     31 |     10 |

| 12:30:20 | 17:30:45 | 05:00:25        | +    |    5 |      0 |     25 |

+----------+----------+-----------------+------+------+--------+--------+

If you’re working with date or date-and-time values, the TIMESTAMPDIFF() function provides another way to calculate intervals, and it enables you to specify the units in which intervals should be expressed. It has this syntax:

TIMESTAMPDIFF(unit,val1,val2)

unit is the interval unit and val1 and val2 are the values between which to calculate the interval. With TIMESTAMPDIFF(), you can express an interval many different ways:

mysql>SET @dt1 = '1900-01-01 00:00:00', @dt2 = '1910-01-01 00:00:00';

mysql> SELECT

    -> TIMESTAMPDIFF(MINUTE,@dt1,@dt2) AS minutes,

    -> TIMESTAMPDIFF(HOUR,@dt1,@dt2) AS hours,

    -> TIMESTAMPDIFF(DAY,@dt1,@dt2) AS days,

    -> TIMESTAMPDIFF(WEEK,@dt1,@dt2) AS weeks,

    -> TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years;

+---------+-------+------+-------+-------+

| minutes | hours | days | weeks | years |

+---------+-------+------+-------+-------+

| 5258880 | 87648 | 3652 |   521 |    10 |

+---------+-------+------+-------+-------+

The allowable unit specifiers are FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. Note that each of these unit specifiers is given in singular form, not plural.
Be aware of these properties of TIMESTAMPDIFF():

• Its value is negative if the first temporal value is greater then the second, which is opposite the order of the arguments for DATEDIFF() and TIMEDIFF().
• Despite the TIMESTAMP in its name, the TIMESTAMPDIFF() function arguments are not limited to the range of the TIMESTAMP data type.
• TIMESTAMPDIFF() requires MySQL 5.0 or higher. For older versions of MySQL, use one of the other interval-calculation techniques described in this section.

Calculating intervals using basic units

Another strategy for calculating intervals is to work with basic units such as seconds or days using this strategy:

1. Convert the temporal values that you’re working with to basic units.
2. Take the difference between the values to calculate the interval, also in basic units.
3. If you want the result as a temporal value, convert it from basic units to the appropriate type.

The conversion functions involved in implementing this strategy depend on the types of the values between which you’re calculating the interval:

• To convert between time values and seconds, use TIME_TO_SEC() and SEC_TO_TIME().
• To convert between date values and days, use TO_DAYS() and FROM_DAYS().
• To convert between date-and-time values and seconds, use UNIX_TIMESTAMP() and FROM_UNIXTIME()


Time interval calculation using basic units

To calculate intervals in seconds between pairs of time values, convert them to seconds with TIME_TO_SEC(), and then take the difference. To express the resulting interval as a TIME value, pass it to SEC_TO_TIME(). The following statement calculates the intervals between the t1 and t2 columns of the time_val table, expressing each interval both in seconds and as a TIME value:

mysql>SELECT t1, t2,

    -> TIME_TO_SEC(t2) - TIME_TO_SEC(t1) AS 't2 - t1 (in seconds)',

    -> SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1)) AS 't2 - t1 (as TIME)'

    -> FROM time_val;

+----------+----------+----------------------+-------------------+

| t1       | t2       | t2 - t1 (in seconds) | t2 - t1 (as TIME) |

+----------+----------+----------------------+-------------------+

| 15:00:00 | 15:00:00 |                    0 | 00:00:00          |

| 05:01:30 | 02:30:20 |                -9070 | -02:31:10         |

| 12:30:20 | 17:30:45 |                18025 | 05:00:25          |

+----------+----------+----------------------+-------------------+

Date or date-and-time interval calculation using basic units

When you calculate an interval between dates by converting both dates to a common unit in relation to a given reference point and take the difference, the range of values that you’re working with determines which conversions are available:

• DATE, DATETIME, or TIMESTAMP values dating back to 1970-01-0100:00:00 UTC—the date of the Unix epoch—can be converted to seconds elapsed since the epoch. If both dates lie within that range, you can calculate intervals to an accuracy of one second.
• Older dates from the beginning of the Gregorian calendar (1582) on can be converted to day values and used to compute intervals in days.
• Dates that begin earlier than either of these reference points present more of a problem. In such cases, you may find that your programming language offers computations that are not available or are difficult to perform in SQL. If so, consider processing date values directly from within your API language. (For example, the Date::Calc and Date::Manip modules are available from CPAN for use within Perl scripts.)

To calculate an interval in days between date or date-and-time values, convert them to days with TO_DAYS(), and take the difference:

mysql>SELECT TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05') AS days;

+------+

| days |

+------+

|  210 |

+------+

For an interval in weeks, do the same thing and divide the result by seven:

mysql>SELECT (TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05')) / 7 AS weeks;

+---------+

| weeks   |

+---------+

| 30.0000 |

+---------+

You cannot convert days to months or years by simple division, because those units vary in length. For calculations to yield date intervals expressed in those units, use the TIMESTAMPDIFF() function discussed earlier in this section.
For date-and-time values occurring within the TIMESTAMP range of 1970 to 2037, you can determine intervals to a resolution in seconds using the UNIX_TIMESTAMP() function. For example, the number of seconds between dates that lie two weeks apart can be computed like this:

mysql>SET @dt1 = '1984-01-01 09:00:00';

mysql> SET @dt2 = @dt1 + INTERVAL 14 DAY;

mysql> SELECT UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1) AS seconds;

+---------+

| seconds |

+---------+

| 1209600 |

+---------+

To convert the interval in seconds to other units, perform the appropriate arithmetic operation. Seconds are easily converted to minutes, hours, days, or weeks:

mysql>SET @interval = UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1);

mysql> SELECT @interval AS seconds,

    -> @interval / 60 AS minutes,

    -> @interval / (60 * 60) AS hours,

    -> @interval / (24 * 60 * 60) AS days,

    -> @interval / (7 * 24 * 60 * 60) AS weeks;

+---------+------------+----------+---------+--------+

| seconds | minutes    | hours    | days    | weeks  |

+---------+------------+----------+---------+--------+

| 1209600 | 20160.0000 | 336.0000 | 14.0000 | 2.0000 |

+---------+------------+----------+---------+--------+

To produce integer values (no fractional part), use the FLOOR() function. This applies to several of the following examples as well.
For values that occur outside the TIMESTAMP range, you can use an interval calculation method that is more general (but messier):

• Take the difference in days between the date parts of the values and multiply by 24 × 60 × 60 to convert to seconds.
• Offset the result by the difference in seconds between the time parts of the values.

Here’s an example, using two date-and-time values that lie a week apart:

mysql>SET @dt1 = '1800-02-14 07:30:00';

mysql> SET @dt2 = @dt1 + INTERVAL 7 DAY;

mysql> SET @interval =

    ->   ((TO_DAYS(@dt2) - TO_DAYS(@dt1)) * 24*60*60)

    ->   + TIME_TO_SEC(@dt2) - TIME_TO_SEC(@dt1);

mysql> SELECT @interval AS seconds, SEC_TO_TIME(@interval) AS TIME;

+---------+-----------+

| seconds | TIME      |

+---------+-----------+

| 604800  | 168:00:00 |

+---------+-----------+

Do You Want an Interval or a Span?

When you take a difference between dates (or times), consider whether you want an interval or a span. Taking a difference between dates gives you the interval from one date to the next. If you want to know the range spanned by the two dates, you must add a unit. For example, it’s a three-day interval from 2002-01-01 to 2002-01-04, but together they span a range of four days. If you’re not getting the results you expect from a difference-of-values calculation, consider whether you need to apply an “off-by-one” correction.

Info lebih jelas : http://answers.oreilly.com/topic/163-how-to-calculate-the-interval-between-two-dates-or-times-in-mysql