Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: Preventing Repeated Results in Query

    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.

    Code:
    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


    Code:
    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...

    Code:
    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:

    Code:
    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    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 AS a
          INNER JOIN AMS_SM_External_Education_Copy AS b
             ON (b.Org_ID=a.Org_ID)
    	WHERE  a.Course_Description IS NOT NULL
           AND a.Course_Grade IS NOT NULL
           AND a.Student_ID = '162'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •