Excessive use of Inner Join for more than 3 tables
- by Archangel08
Good Day,
I have 4 tables on my DB (not the actual name but almost similar) which are the ff: employee,education,employment_history,referrence employee_id is the name of the foreign key from employee table.
Here's the example (not actual) data:
**Employee**
ID Name Birthday Gender Email
1 John Smith 08-15-2014 Male [email protected]
2 Jane Doe 00-00-0000 Female [email protected]
3 John Doe 00-00-0000 Male [email protected]
**Education**
Employee_ID Primary Secondary Vocation
1 Westside School Westshore H.S SouthernBay College
2 Eastside School Eastshore H.S NorthernBay College
3 Northern School SouthernShore H.S WesternBay College
**Employment_History**
Employee_ID WorkOne StartDate Enddate
1 StarBean Cafe 12-31-2012 01-01-2013
2 Coffebucks Cafe 11-01-2012 11-02-2012
3 Latte Cafe 01-02-2013 04-05-2013
Referrence
Employee_ID ReferrenceOne Address Contact
1 Abraham Lincoln Lincoln Memorial 0000000000
2 Frankie N. Stein Thunder St. 0000000000
3 Peter D. Pan Neverland Ave. 0000000000
NOTE: I've only included few columns though the rest are part of the query.
And below are the codes I've been working on for 3 consecutive days:
$sql=mysql_query("SELECT emp.id,emp.name,emp.birthday,emp.pob,emp.gender,emp.civil,emp.email,emp.contact,emp.address,emp.paddress,emp.citizenship,educ.employee_id,educ.elementary,educ.egrad,educ.highschool,educ.hgrad,educ.vocational,educ.vgrad,ems.employee_id,ems.workOne,ems.estartDate,ems.eendDate,ems.workTwo,ems.wstartDate,ems.wendDate,ems.workThree,ems.hstartDate,ems.hendDate
FROM employee AS emp INNER JOIN education AS educ ON educ.employee_id='emp.id' INNER JOIN employment_history AS ems ON ems.employee_id='emp.id' INNER JOIN referrence AS ref ON ref.employee_id='emp.id'
WHERE emp.id='$id'");
Is it okay to use INNER JOIN this way? Or should I modify my query to get the results that I wanted? I've also tried to use LEFT JOIN but still it doesn't return anything .I didn't know where did I go wrong. You see, as I have thought, I've been using the INNER JOIN in correct manner, (since it was placed before the WHILE CLAUSE). So I couldn't think of what could've possible went wrong.
Do you guys have a suggestion? Thanks in advance.
