ID EMP_ID EMP_NAME
1 emp01 Sam
2 emp02 Jam
3 emp03 Mam
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
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?
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;
generally:
select f1,f2,f3 from t1,t2,t3 where t1.x = t2.y and t1.x = t3.y
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
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.
