SQL Help

[jschofield]

Hello fellow phper's,

 

My problem I am having is not really with php but with SQL. I have ran to everyone I know and looked at websites for help. You guys are my last resort. What I am doing is I have two php scripts. Both scripts have an SQL query. On is a student query to pull student data from a data base. The other is to pull attendance student data from the same DB. My problem is that I am getting two different Student ID's for the same students. Therefore I know I am looking in two different fields but dont know how to make the change as I dont really know SQL. If you are willing to help that would be great. If not, I understand. Thanks everyone...below is my SQL querys.

 

Student Query:

$query = 'SELECT

"PUB.STUDENT-ATND-DETAIL"."STUDENT-ID",

"NAME_stu"."LAST-NAME",

"NAME_stu"."FIRST-NAME",

"NAME_stu"."LANGUAGE-CODE",

"NAME_stu"."INTERNET-ADDRESS",

"STUDENT"."BUS-PM-NUMBER",

"STUDENT"."BUS-AM-NUMBER",

"STUDENT"."OTHER-ID",

"STUDENT_ENTITY"."ENTITY-ID",

("ENTITY"."SCHOOL-YEAR" - "STUDENT"."GRAD-YR" + 12) as "GRADE-LEVEL",

"STUDENT_ENTITY"."STUDENT-STATUS",

"NAME_stu"."PRIMARY-PHONE",

"NAME_1"."SECOND-PHONE-TYPE" as "G1-PHONE2-TYPE",

"NAME_1"."SECOND-PHONE-EXT" as "G1-PHONE2-EXT",

"NAME_1"."SECOND-PHONE" as "G1-PHONE2",

"NAME_1"."THIRD-PHONE-EXT" as "G1-PHONE3-EXT",

"NAME_1"."THIRD-PHONE-TYPE" as "G1-PHONE3-TYPE",

"NAME_1"."THIRD-PHONE" as "G1-PHONE3",

"NAME_2"."SECOND-PHONE" as "G2-PHONE2",

"NAME_2"."SECOND-PHONE-EXT" as "G2-PHONE2-EXT",

"NAME_2"."SECOND-PHONE-TYPE" as "G2-PHONE2-TYPE",

"NAME_2"."THIRD-PHONE" as "G2-PHONE3",

"NAME_2"."THIRD-PHONE-EXT" as "G2-PHONE3-EXT",

"NAME_2"."THIRD-PHONE-TYPE" as "G2-PHONE3-TYPE"

 

FROM

(("PUB"."STUDENT" "STUDENT" join "PUB"."NAME" "NAME_stu" on

"STUDENT"."NAME-ID"="NAME_stu"."NAME-ID"

join "PUB"."STUDENT-FAMILY" "STUDENT_FAMILY_1" on

("STUDENT"."STUDENT-ID"="STUDENT_FAMILY_1"."STUDENT-ID" and "STUDENT_FAMILY_1"."FAMILY-NUMBER" = 1)

JOIN "PUB"."STUDENT-ENTITY" "STUDENT_ENTITY" on

("STUDENT"."STUDENT-ID"="STUDENT_ENTITY"."STUDENT-ID" and "STUDENT_ENTITY"."STUDENT-STATUS" = ' . "'" . 'A' . "'" . ')

JOIN "PUB"."ENTITY" "ENTITY" on

("STUDENT_ENTITY"."ENTITY-ID"="ENTITY"."ENTITY-ID")

join "PUB"."FAMILY-GUARDIAN" "FAMILY_GUARDIAN_1" on

("STUDENT_FAMILY_1"."FAMILY-ID"="FAMILY_GUARDIAN_1"."FAMILY-ID" and "FAMILY_GUARDIAN_1"."NAME-NUMBER" = 1)

join "PUB"."NAME" "NAME_1" on

("FAMILY_GUARDIAN_1"."NAME-ID"="NAME_1"."NAME-ID"))

left join "PUB"."FAMILY-GUARDIAN" "FAMILY_GUARDIAN_2" on

("STUDENT_FAMILY_1"."FAMILY-ID"="FAMILY_GUARDIAN_2"."FAMILY-ID" and "FAMILY_GUARDIAN_2"."NAME-NUMBER" = 2))

left join "PUB"."NAME" "NAME_2" on

("FAMILY_GUARDIAN_2"."NAME-ID"="NAME_2"."NAME-ID")

';

 

Attendance Query:

 

$query =

'select PUB."STUDENT-ATND-DETAIL"."STUDENT-ID", "STUDENT-ATND-DETAIL"."ATND-DATE"

from PUB."STUDENT-ATND-DETAIL", PUB."STUDENT-ENTITY"

where PUB."STUDENT-ATND-DETAIL"."STUDENT-ID" = PUB."STUDENT-ENTITY"."STUDENT-ID"

and PUB."STUDENT-ATND-DETAIL"."ENTITY-ID" = PUB."STUDENT-ENTITY"."ENTITY-ID"

and PUB."STUDENT-ENTITY"."STUDENT-STATUS" = ' . "'" . "A" . "'

" . 'and PUB."STUDENT-ATND-DETAIL"."ENTITY-ID" = ' . "'" . "103" . "'

" . 'and (substring(PUB."STUDENT-ATND-DETAIL"."AAT-ID",1,1) = ' . "'" . "U" . "')

" . 'and PUB."STUDENT-ATND-DETAIL"."ATND-DATE" = ' . "'" . "$todaydate" . "'

" ;