|
rwnuk
Starting Member
3 Posts |
 Posted - 04/23/2012 : 11:19:41
|
I admit, it has been a LONG time since I've done any major programming, but I have two select statements that work independently of each other. I need to combine the results of each select and get one result.
Basically I need to pull data from the students table and teachers table and be able to get the data from each.
Here is the code:
SELECT "PS"."STUDENTS"."FIRST_NAME",
"PS"."STUDENTS"."LAST_NAME",
"PS"."STUDENTS"."MIDDLE_NAME",
"PS"."STUDENTS"."CITY",
"PS"."STUDENTS"."DOB",
"PS"."STUDENTS"."ETHNICITY",
"PS"."STUDENTS"."ENTRYDATE",
"PS"."STUDENTS"."GENDER",
"PS"."STUDENTS"."GRADE_LEVEL",
"PS"."STUDENTS"."HOME_PHONE",
"PS"."STUDENTS"."MAILING_CITY",
"PS"."STUDENTS"."MAILING_GEOCODE",
"PS"."STUDENTS"."MAILING_STATE",
"PS"."STUDENTS"."MAILING_STREET",
"PS"."STUDENTS"."MAILING_ZIP",
"PS"."STUDENTS"."STATE",
"PS"."STUDENTS"."STREET",
"PS"."STUDENTS"."STUDENT_NUMBER",
"PS"."STUDENTS"."STATE_STUDENTNUMBER",
"PS"."STUDENTS"."ZIP",
"PS"."STUDENTS"."ENROLL_STATUS",
"PS"."STUDENTS"."HOME_ROOM",
"PS"."SCHOOLS"."NAME" AS "School",
(SELECT "PS"."CUSTOMTEXT"."VALUE" FROM "PS"."CUSTOMTEXT" WHERE "PS"."CUSTOMTEXT"."KEYNO" = "PS"."STUDENTS"."DCID" AND "PS"."CUSTOMTEXT"."FIELDNO" = 746 AND ROWNUM <=1) Physical_Date, (SELECT "PS"."CUSTOMTEXT"."VALUE" FROM "PS"."CUSTOMTEXT" WHERE "PS"."CUSTOMTEXT"."KEYNO" = "PS"."STUDENTS"."DCID" AND "PS"."CUSTOMTEXT"."FIELDNO" = 50 AND ROWNUM <=1) as POB,
(SELECT "PS"."CUSTOMTEXT"."VALUE" FROM "PS"."CUSTOMTEXT" WHERE "PS"."CUSTOMTEXT"."KEYNO" = "PS"."STUDENTS"."DCID" AND "PS"."CUSTOMTEXT"."FIELDNO" = 248 AND ROWNUM <=1) as InsCo,
(SELECT "PS"."CUSTOMTEXT"."VALUE" FROM "PS"."CUSTOMTEXT" WHERE "PS"."CUSTOMTEXT"."KEYNO" = "PS"."STUDENTS"."DCID" AND "PS"."CUSTOMTEXT"."FIELDNO" = 249 AND ROWNUM <=1) as InsNum
FROM "PS"."STUDENTS" LEFT OUTER JOIN "PS"."SCHOOLS" ON "PS"."STUDENTS"."SCHOOLID" = "PS"."SCHOOLS"."SCHOOL_NUMBER" WHERE "PS"."STUDENTS"."ENROLL_STATUS" = 0
SELECT "PS"."TEACHERS"."CITY" as TCITY,
"PS"."TEACHERS"."FIRST_NAME" AS TFIRST_NAME,
"PS"."TEACHERS"."LAST_NAME" AS TLAST_NAME,
"PS"."TEACHERS"."MIDDLE_NAME" AS TMIDDLE_NAME,
"PS"."TEACHERS"."STATUS",
"PS"."TEACHERS"."STATE" AS TSTATE,
"PS"."TEACHERS"."STREET" AS TSTREET,
"PS"."TEACHERS"."TEACHERNUMBER",
"PS"."TEACHERS"."ZIP" AS TZIP,
"PS"."TEACHERS"."HOME_PHONE" AS THOME_PHONE
FROM "PS"."TEACHERS" WHERE "PS"."TEACHERS"."STATUS" = 1 |
|
Using two selects
