Preventing Repeated Results in Query

Question

Preventing Repeated Results in Query

cajun_sql

SSCommitted

Points: 1842
More actions
July 15, 2011 at 2:11 pm

#239881

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_IDExt_TermYearCourse_DescriptionCourse_SubjectCourse_GradeInstitution_Description
--------------- --------------- ------- ------------------------------- ----------------------- ----------------------- -------------------------
16210321997College AlgebraMATHABryce State College
16210321998College Algebra IIMATHABryce State College
16210321998First AidHSSAUniversity of Des Plaines
16210321998Cardio IHSSAUniversity of Des Plaines
16210321999Cardio IIHSSAUniversity 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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

How about using the DISTINCT Operator?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Nevyn SSChampion Points: 14210 More actions · Answer 2

First, I don't think your test data shows your problem as it only shows 1 student (in which case your query actually works).

However, in a table with multiple students it would get duplicates. You did some joining that wasn't really necessary (that derived table is no different from the base table).

What you are missing is a constraint that the Student IDs have to match

Try:

SELECTa.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 AMS_SM_External_Education_Copy b

ONa.Org_ID = b.Org_ID AND

a.Student_ID = b.Student_ID

WHERE

a.Course_Description IS NOT NULL AND

a.Course_Grade IS NOT NULL AND

a.Student_ID = '162'

It is possible that you should also join on "Admit_Term" depending on how your data is set up (I cant figure out why its on both these tables, and not just on AMS_SM_External_Education_Copy.... what does it represent?), but with the example you gave the above should suffice

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Very nice job posting usable DDL, DML and what you have tried! Provide your desired output relative to the same test data please and we'll see if we can assist.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 4

opc.three (7/15/2011)
Very nice job posting usable DDL, DML and what you have tried! Provide your desired output relative to the same test data please and we'll see if we can assist.

Heh... it must be Friday. 😛 The following (which was in the original post) didn't do it for you?

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Jeff Moden (7/15/2011)
opc.three (7/15/2011)
Very nice job posting usable DDL, DML and what you have tried! Provide your desired output relative to the same test data please and we'll see if we can assist.
Heh... it must be Friday. 😛 The following (which was in the original post) didn't do it for you?
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

Nice one Jeff...are you trolling for brain-dead postings on Friday afternoons again? I know I contribute my share but cut me some slack 😉

If you run the sample code it returns what the poster marked under "I want the output to look like this", i.e. what you quoted, but I think they want things rolled up even further. As in:

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?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato