SELECT w/ INNER JOIN Query on Optional Columns

Question

I have five tables: File2, Branches_of_Study, Schools, Education_Establishments, and Certificates. File2 is the transaction table with all the other tables as reference tables.

File2 is all about the educational background of an employee. The employee has to select from a dropdown list in the site the education establishment first, e.g. Highschool, College, etc. Branch of Study refers to the course the employee has taken, e.g. IT, HRM, Photography, etc.

Upon selecting College, the employee must select a Branch of Study. But when selecting Highschool, selecting a branch of study is not allowed.

I have a gridview in the site that shows all of the employee's educational attainment. Here is my query:

SELECT File2.file2_id,
 File2.candidate_id,
 ee.education_establishment,
 s.school,
 File2.school_others,
 File2.start_date,
 File2.end_date,
 bs1.branch_of_study AS branch_of_study_1,
 c.certificate,
 File2.course_appraisal,
 bs2.branch_of_study AS branch_of_study_2
FROM File2
 INNER JOIN Education_Establishments ee
 ON File2.education_establishment_code = ee.education_establishment_code
 INNER JOIN Schools s
 ON File2.school_code = s.school_code
 INNER JOIN Branches_of_Study bs1
 ON File2.branch_of_study_1_code = bs1.branch_of_study_code
 INNER JOIN Branches_of_Study bs2
 ON File2.branch_of_study_2_code = bs2.branch_of_study_code
 INNER JOIN Certificates c
 ON File2.certificate_code = c.certificate_code
 WHERE File2.candidate_id = 100003 

The query returns only results for the College entries for employee number 100003. I assume this is caused by having null values in the Branch of Study fields for Highschool entries.

I want a query that will show all entries (College and Highschool) and at the same time showing the text value (not the code) from the reference tables.

Answer 1

If joining is "optional", then it is not an inner join, by definition. Consider investigating left joins.

• Jeano Ermitaño: The joins are required, as I want to display the text values of the references tables and not the codes. It is the columns branch_of_study_1 and branch_of_study_2 are optional, and I assume this causes the select query to only return entries in which branch_of_study_1 and branch_of_study_2 are not null.
• Andris Birkmanis: If branch_of_study_1 and branch_of_study_2 are optional (nullable), then File2.branch_of_study_1_code and File2.branch_of_study_2_code are nullable, too. Nulls on any side of an inner join suppress the entire row from the output. Maybe I am missing something. Anyway, I suggest you reduce your problem to a smaller one, having only two tables and one join, for example - right now it may be hiding some relevant details behind unnecessary complexity.
• Jeano Ermitaño: So I tried LEFT JOIN on the columns that are optional, and I can confirm it works. It now returns the College entries together with the Highschool entries. Thank you!