Using two selects - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using two selects

Author

Topic

rwnuk
Starting Member

3 Posts

Posted - 2012-04-23 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 11:57:23

do you want merge data horizontally or vertically?
also how are tables related?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rwnuk
Starting Member

3 Posts

Posted - 2012-04-23 : 12:18:58

The tables are not related. If I need a common field between them, I have one.

As for the merge.. not sure how to answer. Basically I need to grab info from the students table and the teacher table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 12:21:12

By related I meant common field. Can you post which is common field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-23 : 12:48:45

Look at the UNION operator:
http://msdn.microsoft.com/en-us/library/ms180026.aspx

rwnuk
Starting Member

3 Posts

Posted - 2012-04-23 : 12:50:55

"PS"."STUDENTS"."SCHOOLID"
AND
"PS"."TEACHERS"."SCHOOLID"

are both common.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources