Another grouping query - 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)

Another grouping query

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2007-10-01 : 19:06:27

Tables
tblGroups defining family of test
tblTests all the tests sorted
tblTestsDone all the tests done by a student

Each student has to perform the tests for each group in sequence.
I am trying to write 2 sql statements

The 1st one would be showing the next test each student can perform
The 2nd one is all the other tests each could perform after having perform the next allowed one

1st SQL
Looking at the table shown below the result should be for student #1
Gr 1, Test 3 (as already performed Te_Id 1 and Te_Id 2 for Gr_Id 1)
Gr 2, Test 5 (as he already performed Te_Id 4 for Gr_Id 2 )
Gr 3, Test 8 (no tests for Gr_Id 3)

2nd SQL
Gr 2, Test 6
Gr 2, Test 7
Gr 3, Test 9

CREATE TABLE [dbo].[tblGroups] (
[Gr_Id] [int] NOT NULL ,
[Gr_Desc] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
INSERT INTO tblGroups
SELECT 1, 'Group 1' UNION ALL
SELECT 2, 'Group 2' UNION ALL
SELECT 3, 'Another group'
GO

CREATE TABLE [dbo].[tblTests] (
[Te_Id] [int] NULL ,
[Te_Gr_Id] [int] NOT NULL ,
[Te_Nr] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO tblTests
SELECT 1,1,1 UNION ALL
SELECT 2,1,2 UNION ALL
SELECT 3,1,3 UNION ALL
SELECT 4,2,1 UNION ALL
SELECT 5,2,2 UNION ALL
SELECT 6,2,3 UNION ALL
SELECT 7,2,4 UNION ALL
SELECT 8,3,1 UNION ALL
SELECT 9,3,2
GO

CREATE TABLE [dbo].[tblTestsDone] (
[Ted_Id] [int] NULL ,
[Ted_Te_Id] [int] NULL ,
[Ted_St_Id] [int] NULL ,
[Ted_Status] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO tblTestsDone
SELECT 1,1,1,1 UNION ALL
SELECT 1,2,1,1 UNION ALL
SELECT 1,4,1,1

jean-luc
www.corobori.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources