how to display associated values

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 2000 Forums

Transact-SQL (2000)

how to display associated values

Author

Topic

LLatinsky
Starting Member

38 Posts

Posted - 2005-04-28 : 16:53:38

Hello. I have to design a table that will store all course equivalents. There is a Course table that stores all info regarding a particular class.
ClassID ClassName........
1 Basic 101
2 Basic 123
3 DBMS
4 Comp 2 and so on...
While designing a CourseEquivalent table
with ClassID, EquivClassID columns - FKs referencing Course table,
I am debating how to store the data in it ...
If I write out all the entries - like, if ClassID 1 is equivalent to Classid 2 and 2 is the same as 3 - etries in CourseEquivalent table
will be :
1 2
2 1
2 3
3 2
3 1
1 3
Then selecting an Equivalent for ClassID 1 becomes really easy but adds(updates and deletes) - especially deletes, are a problem,and table, though narrow, could be huge.
This table will be used to display course equivalents in multiple reports.
If I store everything I am kind of struggling with the delete - how do I know which links to delete if 1 is broken
If if store only 1 record per link like
1 2
2 3
I cannot get my select to work right.
thank you
Any input will be really appreciated

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-29 : 05:20:54

now that's descriptive...
you know what would help. create table statments, insert into statements for sample data
and desired results based on sample data.

Go with the flow & have fun! Else fight the flow

LLatinsky
Starting Member

38 Posts

Posted - 2005-04-29 : 08:48:50

Well, at this point I am debating which way to store data. I was hoping people did something like this before. I just don't want to reinvent the wheel
Thank you

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-29 : 09:13:24

" just don't want to reinvent the wheel"....That may be so....but your description is too brief (and repeating 1,2,3 everywhere doesn't help either)..for us to EASILY help you.
That's what we need some more help from YOU. And maybe by writing out this larger sample input data....you will actually solve the problem yourself....but clarifying your thinking.

LLatinsky
Starting Member

38 Posts

Posted - 2005-04-29 : 12:38:36

use tempdb
go

CREATE TABLE [EducationClass] (
[CourseCodeID] [int] IDENTITY (1, 1) NOT NULL ,
[ClassCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vsKeyword] [int] NULL ,
[vsCategory] [int] NULL ,
[RecertificationPeriod] [smallint] NULL ,
[vsRecertificationUnits] [int] NULL ,
[Duration] [int] NULL ,
[vsDurationUnits] [int] NULL ,
[CollegeCreditHours] [decimal](4, 1) NULL ,
[OtherCreditHours] [decimal](4, 1) NULL ,
[DateChanged] [datetime] NOT NULL ,
[ChangeUser] [int] NULL ,
[InactiveFlag] [bit] NOT NULL CONSTRAINT [DF_ClassInactive] DEFAULT (0)
CONSTRAINT [PK_EducationClass] PRIMARY KEY CLUSTERED
(
[CourseCodeID]
) ON [PRIMARY] ,
CONSTRAINT [XAK1_EducationClass] UNIQUE
(
[ClassCode]
)
) ON [PRIMARY]
GO

CREATE TABLE [CourseCodeEquivalent] (
[CourseCodeID] [int] NOT NULL ,
[EquivCourseCodeID] [int] NOT NULL ,
CONSTRAINT [PK_CourseCodeEq_EducationClass] PRIMARY KEY CLUSTERED
(
[CourseCodeID],
[EquivCourseCodeID]
) ON [PRIMARY] ,
CONSTRAINT [FK_CourseCodeEq_EducationClass1] FOREIGN KEY
(
[CourseCodeID]
) REFERENCES [EducationClass] (
[CourseCodeID]
),
CONSTRAINT [FK_CourseCodeEq_EducationClass2] FOREIGN KEY
(
[EquivCourseCodeID]
) REFERENCES [EducationClass] (
[CourseCodeID]
)
) ON [PRIMARY]

GO
INSERT INTO [EducationClass] ( [ClassCode], [Title], [DateChanged] , [ChangeUser] )
SELECT 'CIS','Comp Info1', getdate(),1
union all
SELECT 'BIS','Comp Info2', getdate(),1
union all
SELECT 'AIS','Comp Info3', getdate(),1
union all
SELECT 'FA','First Aid', getdate(),1
go

select*from [EducationClass]
go
-- there may be many classes that are equivalent to each other
go

-- suppose ClassCodeIDs 1,2,3 are equivalents
-- 2 ways to store
WAY # 1
INSERT INTO [CourseCodeEquivalent] ( [CourseCodeID] , [EquivCourseCodeID] )
values(1,2)
go
INSERT INTO [CourseCodeEquivalent] ( [CourseCodeID] , [EquivCourseCodeID] )
values(3,4)
go
/*the select from this table should show for classID 1
EquivCourseCodeID
2
3- I do not know how to write this select*/

WAY # 2

IF OBJECT_ID('dbo.addCourseCodeEquivalent') IS NOT NULL
DROP PROCEDURE dbo.addCourseCodeEquivalent
GO
CREATE PROCEDURE dbo.addCourseCodeEquivalent

@CourseCodeID int ,
@EquivCourseCodeID int

AS SET NOCOUNT ON

DECLARE @Return int
declare @Courses table (CourseCodeID int, EquivCourseCodeID int)
INSERT INTO @Courses (CourseCodeID , EquivCourseCodeID)
SELECT @CourseCodeID , @EquivCourseCodeID
UNION
SELECT @EquivCourseCodeID, @CourseCodeID
UNION
SELECT CourseCodeID , EquivCourseCodeID
FROM CourseCodeEquivalent WHERE
CourseCodeID = @CourseCodeID OR EquivCourseCodeID = @CourseCodeID
UNION
SELECT CourseCodeID , EquivCourseCodeID
FROM CourseCodeEquivalent WHERE
CourseCodeID = @EquivCourseCodeID OR EquivCourseCodeID = @EquivCourseCodeID
set @return = @@error
if @return <> 0 goto errhandler
--select*from @courses
INSERT INTO @Courses (CourseCodeID , EquivCourseCodeID)
SELECT t.CourseCodeID ,t1.EquivCourseCodeID
from @Courses t cross join @Courses t1 where t.CourseCodeID <>t1.EquivCourseCodeID
set @return = @@error
if @return <> 0 goto errhandler
--select*from @courses

INSERT INTO dbo.CourseCodeEquivalent(CourseCodeID, EquivCourseCodeID)
SELECT distinct CourseCodeID, EquivCourseCodeID
FROM @Courses p WHERE NOT EXISTS(SELECT 1 FROM dbo.CourseCodeEquivalent l
WHERE (p.CourseCodeID= l.CourseCodeID AND p.EquivCourseCodeID = l.EquivCourseCodeID)
OR (p.CourseCodeID = l.EquivCourseCodeID AND p.EquivCourseCodeID = l.CourseCodeID))
set @return = @@error
if @return <> 0 goto errhandler
RETURN 0
ErrHandler:

RETURN @Return

go

exec dbo.addCourseCodeEquivalent 1,2
go
exec dbo.addCourseCodeEquivalent 2,3
go
select*from coursecodeequivalent where coursecodeid = 1
go
-- now if a link between 1 and 2 is deleted or 2 is updated to some other class, I don't know what to delete
-- because classes may be added directly or by association
-- event if I add a flag to CourseCodeEquivalent table for direct add
--- I do not know how to write this delete statement
-- drop table coursecodeequivalent

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources