Select statement inside the Where clause

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 2005 Forums

Transact-SQL (2005)

Select statement inside the Where clause

Author

Topic

rockster8
Starting Member

7 Posts

Posted - 2007-07-23 : 11:26:53

Hello all,
I've got a query that works great, although it is kind of slow. But nevertheless, it gives me the results I want.

---------------------
SELECT
distinct s.emplid as ucid,
p.Person_Last_Name as surname,
p.Person_First_Name + ' ' + p.Person_Middle_Name as given_names,
s.Acad_Group as faculty,
s.Primary_Acad_Plan as major,
s.Academic_Level_End_Term as year_of_program,
p.Person_Birth_Date as birth_date
FROM
dbo.D_Student_Career_Term s,
dbo.D_Person_PS p
WHERE
s.Emplid = p.Emplid AND
s.Academic_Load = 'F' AND
Primary_Program_Ind='Y'AND
Term='2077' AND
Registered_Ind='Y' AND
s.Emplid IN(
SELECT distinct emplid
FROM dbo.F_Admission_Applicant_By_Program
WHERE
Current_Application_ind='Y'AND
Admit_Term='2077' AND
Admit_Type IN ('TRN','HS') AND
(
Final_Admission_status IN ('A','T1','T2') OR
Early_Admission_status IN ('P','Y')
)
)
---------------------

For now, I am only able to display ucid, surname, given_names, faculty, major, year_of_program, and birth_date. I would like to display a column called Admit_Type. Note that the Admit_Type can be found in the Select statement inside the Where clause. How can I pull that out so I can also display it?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-23 : 11:37:29

you'll have to make an inner join.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 12:37:42

[code]SELECT DISTINCT s.emplid as ucid,
p.Person_Last_Name as surname,
p.Person_First_Name + ' ' + p.Person_Middle_Name as given_names,
s.Acad_Group as faculty,
s.Primary_Acad_Plan as major,
s.Academic_Level_End_Term as year_of_program,
p.Person_Birth_Date as birth_date
FROM dbo.D_Student_Career_Term AS s
INNER JOIN dbo.D_Person_PS AS p ON p.Emplid = s.Emplid
INNER JOIN (
SELECT emplid
FROM dbo.F_Admission_Applicant_By_Program
WHERE Current_Application_ind = 'Y'
AND Admit_Term = '2077'
AND Admit_Type IN ('TRN', 'HS')
AND (Final_Admission_status IN ('A', 'T1', 'T2') OR Early_Admission_status IN ('P', 'Y'))
) AS x ON x.EmplId = s.Emplid
WHERE s.Academic_Load = 'F'
AND ?.Primary_Program_Ind = 'Y'
AND ?.Term = '2077'
AND ?.Registered_Ind = 'Y'[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources