Combine results into single record ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Combine results into single record ??

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2007-02-09 : 08:28:05

Here is what I'm trying to do. Each student can pick at most two programs. I'm trying to output this select on a single line so it would look something like this.
Student_id--ProgramChoice1--ProgramChoice2
2 Pharm Dental
3 Dental Null
4 Null Dental

Table stuctures are such
There is an applicant table and a program table
The each applicant has one record in the applicant table and can have 1 or 2 records in the program table.

The way my query is working now is its only pulling those applicant who have records in both Dental and Pharm (first record above)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 08:33:36

Replace your two INNER JOIN with LEFT OUTER JOIN and try again.

Peter Larsson
Helsingborg, Sweden

bogey
Posting Yak Master

166 Posts

Posted - 2007-02-09 : 09:11:14

quote:
Originally posted by Peso

Replace your two INNER JOIN with LEFT OUTER JOIN and try again.

Peter Larsson
Helsingborg, Sweden

Peter - Here is what I'm getting.
Name Prog1 Prog2 Year
Jennifer 4 1 2007
test 4 1 2007
Kyle 4 1 2007
Richard 4 1 2007
Jennifer 4 1 2007

What I need is anyone who has applied to either Prog1 or Prog2. What I'm getting is those who have applied to both.

Here is my code:
SELECT UOP_User.first_name, Application_1.application_type AS SURP,
Application_2.application_type AS SPURG, Applicant.app_year
FROM UOP_User LEFT OUTER JOIN
Applicant ON UOP_User.id = Applicant.app_id LEFT OUTER JOIN
Application AS Application_1 ON
UOP_User.id = Application_1.app_id LEFT OUTER JOIN
Application AS Application_2 ON UOP_User.id = Application_2.app_id
WHERE (Application_1.application_type = 4) AND (Application_2.application_type = 1) AND
(Applicant.app_year = 2007)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 09:51:17

Check the JOIN condition in red. That can't be right.

SELECT		UOP_User.first_name,
		Application_1.application_type AS SURP, 
		Application_2.application_type AS SPURG,
		Applicant.app_year
FROM		UOP_User
INNER JOIN	Applicant ON Applicant.app_id = UOP_User.id
LEFT JOIN	Application AS Application_1 ON Application_1.app_id = UOP_User.id AND Application_1.application_type = 4
LEFT JOIN	Application AS Application_2 ON Application_2.app_id = UOP_User.id AND Application_2.application_type = 1
WHERE		Applicant.app_year = 2007

The two UOP_User.id matches application id's?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources