CODEWITHSUNDEEP
sqloracle-database
Eric
Eric

Reputation: 27

Oracle SELECT fails when adding another WHERE condition with INNER JOINs

Connecting to an Oracle 12c database with PHP 5.5.29, OCI8 is installed and verified in php info page.

This code works fine and returns all of the matching students

SELECT pcs.Student_Number as SNUMBER, pcs.STRING_VALUE as PW, s.GUARDIANEMAIL as GEMAIL, s.WEB_ID as LOGIN, s.FIRST_NAME as FN, s.LAST_NAME as LN, pec.EMAILADDRESS as EMAIL
    FROM PVSIS_CUSTOM_STUDENTS pcs
        INNER JOIN STUDENTS s
          ON pcs.STUDENT_NUMBER = s.STUDENT_NUMBER
        INNER JOIN GuardianStudent gs 
          ON s.DCID = gs.STUDENTSDCID
        INNER JOIN PCAS_EMAILCONTACT pec 
          ON gs.GUARDIANID = pec.EXTERNALIDENT
    WHERE pcs.SCHOOLID=9
          AND pcs.FIELD_NAME='web_password' 
          AND s.ENROLL_STATUS=0

However adding another condition to find just one student fails and doesn't seem to return an error, just 0 rows found. I'm using "12345" as an example, and yes it's a number field so I don't need to quote it.

SELECT pcs.Student_Number as SNUMBER, pcs.STRING_VALUE as PW, s.GUARDIANEMAIL as GEMAIL, s.WEB_ID as LOGIN, s.FIRST_NAME as FN, s.LAST_NAME as LN, pec.EMAILADDRESS as EMAIL
    FROM PVSIS_CUSTOM_STUDENTS pcs
        INNER JOIN STUDENTS s
          ON pcs.STUDENT_NUMBER = s.STUDENT_NUMBER
        INNER JOIN GuardianStudent gs 
          ON s.DCID = gs.STUDENTSDCID
        INNER JOIN PCAS_EMAILCONTACT pec 
          ON gs.GUARDIANID = pec.EXTERNALIDENT
    WHERE pcs.SCHOOLID=9
          AND pcs.FIELD_NAME='web_password' 
          AND s.ENROLL_STATUS=0 
          AND s.STUDENT_NUMBER=12345

Upvotes: 0

Views: 86

Answers (3)

Moptan
Moptan

Reputation: 336

I can't see any obvious "faults" with your sql. However, if student 12345 is missing in any way data from (dcis, studentsdcid, guardianid, externalident, student_number) or there are no matching data in any of the tables. Then no record will be returned since you are using inner joins.

2 suggestions:

*Try changing the inner joins to left joins when you try searching for student 12345. If it returns any data you will then see what might be missing

*Try searching for a student which appear in the list from the first sql statement. If this still does not return any record then you might have to restructure your sql statement

Upvotes: 1

Subash
Subash

Reputation: 895

I am not writing the whole query,just a sample one below Using OR Condition:

SELECT pcs.Student_Number as SNUMBER, pcs.STRING_VALUE as PW,
s.GUARDIANEMAIL as GEMAIL, s.WEB_ID as LOGIN, s.FIRST_NAME as FN,
s.LAST_NAME as LN, pec.EMAILADDRESS as EMAIL
FROM PVSIS_CUSTOM_STUDENTS pcs
    INNER JOIN STUDENTS s
      ON pcs.STUDENT_NUMBER = s.STUDENT_NUMBER
    INNER JOIN GuardianStudent gs 
      ON s.DCID = gs.STUDENTSDCID
    INNER JOIN PCAS_EMAILCONTACT pec 
      ON gs.GUARDIANID = pec.EXTERNALIDENT
WHERE pcs.SCHOOLID=9
      AND pcs.FIELD_NAME='web_password' 
      AND s.ENROLL_STATUS=0 
      AND s.STUDENT_NUMBER=12345
      OR
      <other Student details>

Upvotes: 0

Rahul
Rahul

Reputation: 77876

That's probably cause no any record matches with those condition in place since it's AND. Try making that last condition to a OR condition and see like

WHERE pcs.SCHOOLID=9
      AND pcs.FIELD_NAME='web_password' 
      AND s.ENROLL_STATUS=0 
      OR s.STUDENT_NUMBER=12345

Upvotes: 0

Related Questions