Join Multiple tables in my-sql

Question

Hello All,

Parent Table

ID EMP_ID EMP_NAME

1 emp01 Sam

2 emp02 Jam

3 emp03 Mam

Child Table 1

ID EMP_ID EMP_ADDRESS

1 1 A Street

2 1 B Street

3 2 Z Street

4 3 L Street

5 3 M Street

6 3 N Street

Child Table 2

ID EMP_ID EMP_PHONE

1 1 123456789

2 1 456789123

3 3 456987321

4 3 465987321

5 3 321651213

If i pass the input as 'emp01', i need to get all the information from parent and child tables

Parent table ID and Child emp_id forms the foreign key relationships

How can this be achieved?

Answer 1

generally:

select f1,f2,f3 from t1,t2,t3 where t1.x = t2.y and t1.x = t3.y

Answer 2

yes, but you would get duplicate information (via Cartesian result) based on multiple entries in the child tables

select a1.ID,
       a1.Emp_ID,
       a1.Emp_Name,
       b1.Emp_Address,
       b1.Emp_Phone
   from 
      EmployeeTable a1,
      AddressTable b1,
      PhoneTable c1
   where 
          a1.Emp_ID = {your parameter ID value}
      and a1.ID = b1.Emp_ID
      and a1.ID = c1.Emp_ID;

So, with one employee that has 2 address lines and 2 phone numbers would get 4 lines returned in the query...

ID 1, Address 1, Phone 1 ID 1, Address 1, Phone 2 ID 1, Address 2, Phone 1 ID 1, Address 2, Phone 2

Answer 3

There are two ways of doing this:

select emp_name, emp_address, emp_phone
from   t1, t2, t3
where  t1.id = t2.emp_id and
       t1.id = t3.emp_id;

or

select emp_name, emp_address, emp_phone
from   t1
join   t2 on (t2.emp_id = t1.id)
join   t3 on (t3.emp_id = t1.id);

They're both optimized the same, so there really isn't any reason to prefer one over the other except the second form also works for left joins and the like.

But see @Drapp's caution about Cartesian joins. The whole reason for putting addresses and phone numbers in separate tables is so that you can have multiple addresses and phone numbers for a single employee, and doing it this way will give multiple results.

Answer 4

select p.ID,
       p.Emp_ID,
       p.Emp_Name,
       c1.Emp_address,
       c1.Emp_id,
       c1.id,
       c2.Emp_Phone,
       c2.Emp_id,
       c2.id
   FROM 
      parent p
      LEFT JOIN child1 c1 ON c1.id = p.id
      LEFT JOIN child2 c2 ON c2.id = p.id
   WHERE p.Emp_ID ='emp01';

**EDIT

select p.ID,
           GROUP_CONCAT(c2.Emp_Phone)
       FROM 
          parent p
          LEFT JOIN child1 c1 ON c1.id = p.id
          LEFT JOIN child2 c2 ON c2.id = p.id
       WHERE p.Emp_ID ='emp01'
        GROUP BY p.Emp_ID;