SQL Cartesian Problem

This could be simple but I am going crazy tring to figure this out.


There are four tables

1. Courses - hold Course data
2. Programs - holds only ProgramID and Program Text
3. Prerequisites - indicates courses that are required before registering for new course
3. Corequisites - indicate courses that you can do along with (at the same time) the new course

Here is my SQL statement:

SELECT  C.CourseID, C.Title, C.Description, C.GoLive, P.ProgramName,
Pr.Prerequisite, Pr.PrerequisiteTracker, Co.Corequisite, Co.CorequisiteTracker
From tblCourses AS C, tblPrograms AS P, tblPrerequisites AS Pr, tblCorequisites AS Co
Where C.CourseID = @courseid
AND C.ProgramID = P.ProgramID
AND Pr.CourseID = C.CourseID
AND Pr.CourseID = Co.CourseID
AND Co.CourseID = C.CourseID

Group By C.CourseID, Pr.PrerequisiteTracker, Co.CorequisiteTracker,  C.Title, C.Description, C.GoLive, P.ProgramName,
Pr.Prerequisite, Co.Corequisite
Order By C.CourseID

Here is what is returned (I have included headers):

CourseID     Title                                Description                                       GoLive                     Prerequisite  PrerequisiteID     Corequisite    CorequisiteID
CMCE1100  Presentations with Power  Improving your presentation skills      0 Communications   CM2200        62                     PP6754         38
CMCE1100  Presentations with Power  Improving your presentation skills      0 Communications   CM3400        63                     PP6754         38
CMCE110   Presentations with Power  Improving your presentation skills      0 Communications   CM2200        62                     RR5432         39
CMCE1100  Presentations with Power  Improving your presentation skills      0 Communications   CM3400        63                     RR5432         39

I don't want all rows returned since there is a duplication in the prerequisite and corequisite data.

Here is the result I want:

CourseID     Title                                Description                                       GoLive                     Prerequisite  PrerequisiteID     Corequisite    CorequisiteID
CMCE1100  Presentations with Power  Improving your presentation skills      0 Communications   CM2200        62                     PP6754         38
CMCE1100  Presentations with Power  Improving your presentation skills      0 Communications   CM3400        63                     RR5432         39



Can you help me out of these Cartesian like dilemma?  I have tried Distinct but it does nothing.

Thank you

Vince

Here are the create table scripts if that helps:

USE [dbContEd]
GO
/****** Object:  Table [dbo].[tblCourses]    Script Date: 02/01/2007 13:54:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCourses](
      [CourseID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [CourseTrackerID] [int] IDENTITY(1,1) NOT NULL,
      [Title] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Description] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ProgramID] [int] NULL,
      [RegularHours] [int] NULL,
      [FastTrackHours] [int] NULL,
      [LabHours] [int] NULL,
      [Tuition] [float] NULL,
      [MaxClass] [int] NULL,
      [MinClass] [int] NULL,
      [DateDeveloped] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DateRevised] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Comments] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [OnlineDelivery] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblCourses_OnlineDelivery]  DEFAULT ((0)),
      [BlendedDelivery] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblCourses_BlendedDelivery]  DEFAULT ((1)),
      [GoLive] [int] NULL CONSTRAINT [DF_tblCourses_GoLive]  DEFAULT ((0)),
 CONSTRAINT [PK_tblCourses] PRIMARY KEY CLUSTERED
(
      [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Go

USE [dbContEd]
GO
/****** Object:  Table [dbo].[tblPrograms]    Script Date: 02/01/2007 13:54:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblPrograms](
      [ProgramID] [int] IDENTITY(1,1) NOT NULL,
      [ProgramName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tblPrograms] PRIMARY KEY CLUSTERED
(
      [ProgramID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
Go

USE [dbContEd]
GO
/****** Object:  Table [dbo].[tblPrerequisites]    Script Date: 02/01/2007 13:55:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblPrerequisites](
      [CourseID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Prerequisite] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PrerequisiteTracker] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblPreRequisites] PRIMARY KEY CLUSTERED
(
      [PrerequisiteTracker] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
Go
USE [dbContEd]
GO
/****** Object:  Table [dbo].[tblCorequisites]    Script Date: 02/01/2007 13:55:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCorequisites](
      [CourseID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Corequisite] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CorequisiteTracker] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblCorequisites] PRIMARY KEY CLUSTERED
(
      [CorequisiteTracker] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Thank

Vince Stack


Start Free Trial