Query help - SQL Server Forums

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)

Query help

Author

Topic

stephe40
Posting Yak Master

218 Posts

Posted - 2004-04-27 : 15:26:59

I have 4 tables, objectives, outcomes, objoutmap, and outcomelog. To "meet" an objective a student must complete certian outcomes. The table that stores what outcomes are required to be completed to meet an objective is objoutmap. Outcomelog stores a studentID and what outcomes that student has completed.

What I need the query to do is give me a list of objectives and students that have met those objectives.

Here is the DML


CREATE TABLE objectives (
	objID int NOT NULL ,
	objName varchar (50) NOT NULL ,
	CONSTRAINT PK_objectives PRIMARY KEY  CLUSTERED (objID)
)
GO

CREATE TABLE outcomes (
	outcomeID int NOT NULL ,
	outletter char (1),
	CONSTRAINT PK_outcomes PRIMARY KEY  CLUSTERED (outcomeID)
)
GO

CREATE TABLE objoutMap (
	mapid int IDENTITY (1, 1) NOT NULL ,
	objectiveID int NOT NULL ,
	outcomeID int NOT NULL ,
	CONSTRAINT PK_objoutMap PRIMARY KEY  CLUSTERED (mapid)
) 
GO

CREATE TABLE [outcomeLog] (
	[outcomeLogID] [int] IDENTITY (1, 1) NOT NULL ,
	[sid] [varchar] (10),
	[outcomeID] [int] NULL ,
	CONSTRAINT [PK_outcomeLog] PRIMARY KEY  CLUSTERED (outcomeLogID) 
) 
GO

insert into outcomes (outcomeID, outletter)
select 1, 'a' union
select 2, 'b' union
select 3, 'c' union
select 4, 'd' 

go

insert into objectives (objID, objName)
select 1, 'a' union
select 2, 'bbbb' union
select 3, 'ccc' union
select 4, 'dddd' 

go

insert into outcomeLog (sid, outcomeID)
select '1234567', 1 union
select '1234567', 2 union
select '2345678', 1 union
select '2345678', 2 union
select '2345678', 3

go

insert into objoutmap ( objectiveId, outcomeID)
select 1, 1 union
select 1, 2 union
select 4, 1 union
select 4, 3

An example of what I need would be:

objectiveID, studentID
1, 1234567
1, 2345678
4, 2345678

Student 1234567 has completed outcome 1 and 2 so he qualifies to meet objective 1, but not 4. Student 2345678 has completed outcomes 1, 2, and 3 so he meets objective 1 and 4 because objective 1 requires outcomes 1 and 2, and objective 4 requires outcome 1, and 3.

I hope this makes sense. I have been working on this for a while now and Im debating if its even possible with regular sql.

- Eric

stephe40
Posting Yak Master

218 Posts

Posted - 2004-04-27 : 17:02:07

This is what i have come up with so far. Given a studentID and a objective, it returns 1 or nothing depending of the student has al the outcomes of the objective.

declare @objectiveID int, @studentID varchar(10)
set @objectiveID = 4
set @studentID = '1234567'


select 1 from
(
select a.outcomeID as out1, b.outcomeID as out2 from 
(select ol1.outcomeID from outcomelog ol1 where ol1.sid = @studentID) a 
  inner join (select oom.outcomeID from objoutmap oom where oom.objectiveID = @objectiveID) b
    on a.outcomeID = b.outcomeID
) x
having count(*) = (select count(*) from objoutmap oom2 where oom2.objectiveID = @objectiveID)

I thought i could then do a cross join between the students and objectives and run the query for each row. But I cant get that to work. I keep getting an error.

select s.sid, o.objID from students as s cross join objectives as o
where 1 in (

select 1 from
(
select a.outcomeID as out1, b.outcomeID as out2 from 
(select ol1.outcomeID from outcomelog ol1 where ol1.sid = s.sid) a 
  inner join (select oom.outcomeID from objoutmap oom where oom.objectiveID = o.objID) b
    on a.outcomeID = b.outcomeID
) x
having count(*) = (select count(*) from objoutmap oom2 where oom2.objectiveID = o.objID)

)

results in

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 's' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'o' does not match with a table name or alias name used in the query.

- Eric

afterburn
Starting Member

28 Posts

Posted - 2004-04-27 : 23:48:01

the tables are nested to far in for the outmost to see them.

Working on a solution now.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-04-28 : 00:25:51

Eric,

You are making more complicated than it needs to be (I think) ....it is a simple division query...


declare @objectiveID int, @studentID varchar(10)
set @objectiveID = 1
set @studentID = '1234567'

Select sid, OM.objectiveId
from objoutmap OM
INNER JOIN outcomeLog OL ON OL.OutComeID = OM.OutComeID
--where sid = @StudentID AND OM.objectiveId = @objectiveID
GROUP BY OM.objectiveId, sid
HAVING COUNT(*) = (SELECT COUNT(*) from objoutmap OMX WHERE OMX.ObjectiveID = OM.ObjectiveID)

Just remove the comments for a specific student/objective......

DavidM

"If you are not my family or friend, then I will discriminate against you"

stephe40
Posting Yak Master

218 Posts

Posted - 2004-04-28 : 01:37:50

Well byrmol, you just made my day, err night. :) I just tested it against some small test data and it seems to work fine. I'll have to put it through some more testing in the morning, but everything looks good. It makes sense though, I was close, I just couldnt make that last step. Thanks again.

- Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources