0

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.

0

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

  • 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. – Jeano Ermitaño Jun 16 '15 at 2:13
  • 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. – Andris Birkmanis Jun 16 '15 at 2:20
  • 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! – Jeano Ermitaño Jun 16 '15 at 2:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.