Skip to main content

How to join more than two table sql

Joining Tables:
                        Join is used to relate multiple table. It is used to avoid redundant data. It just follow the concept Divide and Conquer. Instead maintaining the whole redundant data in one table and wasting of storage. we divide table into multiple part and as join the table. we join the table as what information is need. Also join save store memory, as below example table for employee thomas the dept_id is 1, instead maintaining  the character storage. 
Lets join 6 table 
employee
 id     Emp_name     Dept_id
 1 Thomas 1
 2             Jebas 2
 3 Mark             3
 4         Harry 4
 In this table employee,  the address of employee are divided as below.
Deparment
 Dept_id Dept_name
     1 Director
     2 Manager
     3 Employee
     4 HR

country_table
 country_id country_name
 1 INDIA
 2 UK
 3 US

state_table
 state_id country_id state_name
 1 1 Tamil Nadu
 2 1 Andhra Pradesh
 3 1 Kerala
 4 3 New York
 5 2 England


city_table
 city_id state_id city_name
 1 1 Tirunelveli
 2 1 Tuticorin
 3 3 Thiruvananthapuram
 4 4 Beacon
 5 5 Gloucester

Address_table
 Emp_id city_id state_id country_id
 1 1 1 1
 2 2 1 1
 3 4 4 3
 4 5 5 2

SELECT  Emp_name
,dept_name
      ,city_name
      ,state_name
      ,country_name
  FROM Employee, dept_table,
 city_table,
 state_table,
 country_table,
 Adresss_table
whereEmployee.dept_id =dept_table.dept_id AND 
Employee.id=Adresss_table.address_id
AND Adresss_table.city_id =city_table.city_id 
AND Adresss_table.state_id =state_table.state_id 
AND Adresss_table.country_id =country_table.country_id 
This statement return as  
 Emp_name dept_name city_name state_name country_name
 Thomas Director Tirunelveli Tamil Nadu INDIA
 Jebas Manager Tuticorin Tamil Nadu INDIA
 Mark Employee Beacon New YorkUS
 Harry HR Gloucester EnglandUK

Also we filter the result  as we need

SELECT  Emp_name
,dept_name
      ,city_name
      ,state_name
      ,country_name
  FROM Employee, dept_table,
 city_table,
 state_table,
 country_table,
 Adresss_table
whereEmployee.dept_id =dept_table.dept_id AND 
Employee.id=Adresss_table.address_id
AND Adresss_table.city_id =city_table.city_id 
AND Adresss_table.state_id =state_table.state_id 
AND Adresss_table.country_id =country_table.country_id 
AND country_table.country_name='INDIA'

This statement the employee whose country is india
 Emp_name dept_name city_name state_name country_name
 Thomas Director Tirunelveli Tamil Nadu INDIA
 Jebas Manager Tuticorin Tamil Nadu INDIA


SELECT  Emp_name
,dept_name
      ,city_name
      ,state_name
      ,country_name
  FROM Employee, dept_table,
 city_table,
 state_table,
 country_table,
 Adresss_table
whereEmployee.dept_id =dept_table.dept_id AND 
Employee.id=Adresss_table.address_id
AND Adresss_table.city_id =city_table.city_id 
AND Adresss_table.state_id =state_table.state_id 
AND Adresss_table.country_id =country_table.country_id 
AND country_table.country_name='INDIA' 
AND city_table.city_name='Tirunelveli'.
This statement the employee whose city name is Tirunelveli
 Emp_name dept_name city_name state_name country_name
 Thomas Director Tirunelveli Tamil Nadu INDIA



Thank You. Like, Share and Subscribe 👍 Followlect.com


Comments