Hi,
I am running a query that joins two tables.
The results are returned in duplicates - Is it possible to get a return that doesn't repeat?
Below are my DDL, DML, and expected result.
USE [cop]
GO
/****** Object: Table [dbo].[AMS_SM_External_Course_Combined_Copy] Script Date: 07/15/2011 10:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AMS_SM_External_Course_Combined_Copy](
[External_Course_ID] [int] IDENTITY(1,1) NOT NULL,
[Student_ID] [int] NULL,
[Admit_Term] [nvarchar](255) NULL,
[Org_ID] [nvarchar](255) NULL,
[Ext_Term] [nvarchar](255) NULL,
[Year] [int] NULL,
[Course_Description] [nvarchar](255) NULL,
[Course_Subject] [nvarchar](255) NULL,
[Course_Grade] [nvarchar](255) NULL,
CONSTRAINT [PK_AMS_SM_External_Course_Combined_Copy] PRIMARY KEY CLUSTERED
(
[External_Course_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[AMS_SM_External_Education_Copy] Script Date: 07/15/2011 10:02:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AMS_SM_External_Education_Copy](
[External_Education_ID] [int] IDENTITY(1,1) NOT NULL,
[Student_ID] [int] NULL,
[Admit_Term] [nvarchar](255) NULL,
[Org_ID] [nvarchar](255) NULL,
[Institution_Description] [nvarchar](255) NULL,
[Degree] [nvarchar](255) NULL,
[Degree_Description] [nvarchar](255) NULL,
[TTL_GPA] [decimal](4, 2) NULL,
CONSTRAINT [PK_AMS_SM_External_Education_Copy] PRIMARY KEY CLUSTERED
(
[External_Education_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_AMS_SM_External_Education_Copy_AMS_SM_Bio_Copy] Script Date: 07/15/2011 10:02:34 ******/
ALTER TABLE [dbo].[AMS_SM_External_Education_Copy] WITH CHECK ADD CONSTRAINT [FK_AMS_SM_External_Education_Copy_AMS_SM_Bio_Copy] FOREIGN KEY([Student_ID])
REFERENCES [dbo].[AMS_SM_Bio_Copy] ([Student_ID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[AMS_SM_External_Education_Copy] CHECK CONSTRAINT [FK_AMS_SM_External_Education_Copy_AMS_SM_Bio_Copy]
GO
INSERT INTO [cop].[dbo].[AMS_SM_External_Education_Copy]
([Student_ID]
,[Admit_Term]
,[Org_ID]
,[Institution_Description]
,[Degree]
,[Degree_Description]
,[TTL_GPA])
SELECT '162','1032','16208','Bryce State College','AS','Associate of Science','3.20' UNION ALL
SELECT '162','1032','16209','University of Des Plains','BS','Bachelor of Science','3.20'
GO
INSERT INTO [cop].[dbo].[AMS_SM_External_Course_Combined_Copy]
([Student_ID]
,[Admit_Term]
,[Org_ID]
,[Ext_Term]
,[Year]
,[Course_Description]
,[Course_Subject]
,[Course_Grade])
SELECT '162','1032','16208','FALL','1997','College Algebra','MATH','A' UNION ALL
SELECT '162','1032','16208','SP','1998','College Algebra II','MATH','A' UNION ALL
SELECT '162','1032','16209','FALL','1998','First Aid','HSS','A' UNION ALL
SELECT '162','1032','16209','FALL','1998','Cardio I','HSS','A' UNION ALL
SELECT '162','1032','16209','SP','1999','Cardio II','HSS','A'
GO
/********************************************************/
Here's the query I've been trying...
SELECT
a.Student_ID,
a.Ext_Term,
a.Year,
a.Course_Description,
a.Course_Subject,
a.Course_Grade,
b.Institution_Description
FROM
AMS_SM_External_Course_Combined_Copy a
INNER JOIN
(SELECT c.* FROM AMS_SM_External_Education_Copy c
INNER JOIN (SELECT Distinct Org_ID
FROM AMS_SM_External_Education_Copy
GROUP BY Org_ID) d
ON c.Org_ID=d.Org_ID)b
ON
a.Org_ID = b.Org_ID
WHERE
((a.Course_Description IS NOT NULL)
AND (a.Course_Grade IS NOT NULL) AND
(a.Student_ID = '162'))
I want the output to look like this:
Student_ID Ext_Term Year Course_Description Course_Subject Course_Grade Institution_Description
162 1032 1997 College Algebra MATH A Bryce State College
162 1032 1998 College Algebra II MATH A Bryce State College
162 1032 1998 First Aid HSS A University of Des Plaines
162 1032 1998 Cardio I HSS A University of Des Plaines
162 1032 1999 Cardio II HSS A University of Des Plaines
The problem is that the query seems to output each result once for each institution (Org_ID), rather than the course taken only once for its associated institution (Org_ID).
Would using a key composed of the Student_ID and the Org_ID be feasable?
Thanks for any help - much appreciated.
|
