Mysql join query on three tables

Mysql join query on three tables with multiple columns

MySQL: Joins

MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever two or more tables are joined in a SQL statement.

There are different types of MySQL joins:

• MySQL INNER JOIN (or sometimes called simple join)
• MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

Example 1

I have three tables like this:

MySql join three table

Specialisation

sid | s_name
--------------
 1  | test 1
 2  | test 2  

Person

pid | name | sid
------------------
 1  | ABC  |  1
 2  | XYZ  |  2

Timing

tid | time_from | time_to  | pid
----------------------------------
 1  | 08:00:00  | 10:00:00 |  1
 2  | 20:00:00  | 22:00:00 |  1
 3  | 09:00:00  | 14:00:00 |  2
 4  | 19:00:00  | 20:00:00 |  2

Get result something like this form

pid | name | s_name | time_from  | time_to
--------------------------------------------
 1  | ABC  | test 1 | 08:00:00   | 10:00:00

Query

SELECT  a.pid, a.name,
        b.sname,
        c.time_from,
        c.time_to
FROM    person a
        INNER JOIN specialisation b
            ON a.sid = b.sid
        INNER JOIN Timing c
            ON a.pid = c.pid
WHERE   a.sid = 1 and 
        a.name='ABC'  AND 
        c.time_from >= '08:00:00' AND c.time_to <= '10:00:00'

Join tables to person, person table index to specialisation and timing table.

Try

SELECT t.pid, p.name, s.s_name, t.time_from, t.time_to 
     FROM   Specialisation AS s
    left join  Person AS p ON p.sid = s.sid
    left join  Timing AS t ON t.pid = p.pid
    WHERE  s.sid = 1
        AND t.time_from >= 08:00:00' AND t.time_to <= 10:00:00
group by t.tid 

Example 2

I have four table

MySql join four table

receipt

receipt_no | ccid | students_admission_number | students_ccid | reference_number | receipt_date | type | ...
------------------------------------------------------------------------------------------------------------

receiptitems

id | receipt_no | ccid | receipt_head_id | ..... | amount
---------------------------------------------------------

receipthead

id | receipt_no | ccid | receipt_head_id | ..... | amount
---------------------------------------------------------




students

admission_number | ccid | student_name | application_reference_no | ..... 
-------------------------------------------------------------------------




Get result something like this form

Query

select r.receipt_no as receipt_no, r.ccid as ccid,r.students_admission_number as admission_number, (select CONCAT(application_reference_no, ' \n ', student_name) from students where admission_number = r.students_admission_number and ccid = r.students_ccid) as student_name, r.reference_number as reference_number, DATE_FORMAT(receipt_date, '%d-%m-%Y') AS date,receipt_head, amount
from receiptitems
LEFT JOIN receipt as r on (receiptitems.receipt_no = r.receipt_no and receiptitems.ccid = r.ccid)
LEFT JOIN receipthead ON (receipthead.id = receiptitems.receipt_head_id)
where r.type = 'STUDENTS' and r.students_admission_number > 0 and r.ccid = ". $ccid ." and (r.receipt_date BETWEEN '" .$m1."' AND '".$m2."')