October 2, 2007 at 7:38 am
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
SELECT1, 'Group 1' UNION ALL
SELECT2, 'Group 2' UNION ALL
SELECT3, '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
SELECT1,1,1 UNION ALL
SELECT2,1,2 UNION ALL
SELECT3,1,3 UNION ALL
SELECT4,2,1 UNION ALL
SELECT5,2,2 UNION ALL
SELECT6,2,3 UNION ALL
SELECT7,2,4 UNION ALL
SELECT8,3,1 UNION ALL
SELECT9,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
SELECT1,1,1,1 UNION ALL
SELECT1,2,1,1 UNION ALL
SELECT1,4,1,1
Jean-Luc
www.corobori.com
October 2, 2007 at 8:09 am
This almost sounds like homework. Can you post what you've tried?
October 2, 2007 at 8:22 am
Steve Jones - Editor (10/2/2007)
This almost sounds like homework. Can you post what you've tried?
Homework ? Not at all. Let's say that between house building and moving August and September were not productive. I couldn't work as much as I would like for my clients and for the last 3 weeks I have been working too much beeing able to think clear.
Jean-Luc
www.corobori.com
October 2, 2007 at 8:49 am
Here is the first shot at the queries. There could be effeicient ways of doing this as well.
Query 1:
--------
select y.ted_st_id,
x.te_gr_id,
min(x.te_id) te_id
from tbltests x
inner join
( -- to find the students with the latest test that they attended for each group
select a.ted_st_id, b.te_gr_id, coalesce(max(c.te_nr), 0) te_nr
from tbltestsdone a
inner join tbltests b on 1 = 1 -- to get all the groups for each student
left join tbltests c on c.te_id = a.ted_te_id and
c.te_gr_id = b.te_gr_id
group by a.ted_st_id, b.te_gr_id
) y on x.te_gr_id = y.te_gr_id and
x.te_nr > y.te_nr
group by y.ted_st_id, x.te_gr_id
having min(x.te_nr) > max(y.te_nr) -- to get the next available test for each group
Query 2:
--------
select distinct y.ted_st_id, z.te_gr_id, z.te_id
from tbltests x
inner join
( -- to find the students with the latest test that they attended for each group
select a.ted_st_id, b.te_gr_id, coalesce(max(c.te_nr), 0) te_nr
from tbltestsdone a
inner join tbltests b on 1 = 1 -- to get all the groups for each student
left join tbltests c on c.te_id = a.ted_te_id and
c.te_gr_id = b.te_gr_id
group by a.ted_st_id, b.te_gr_id
) y on x.te_gr_id = y.te_gr_id and
x.te_nr > y.te_nr
inner join tbltests z on z.te_gr_id = x.te_gr_id and
z.te_gr_id = y.te_gr_id and
z.te_nr > x.te_nr
where x.te_nr > y.te_nr
October 2, 2007 at 9:37 am
I think I could have spend days and *never* will be able to get this.
Still I am having and issue if tblTestsDone is empty the query doesn't return the 1st test.
Thanks for your help.
Jean-Luc
www.corobori.com
October 2, 2007 at 9:46 am
Since you wanted it specific to each student, the query doesn't return any result (since you don't have any student related information). If you have a separate table for students, that would solve the problem. There should definitely be a student table since you just have the student id in the tbltestsdone table. Here is the first query if you have student table
select y.st_id, x.te_gr_id, min(x.te_id) te_id
from tbltests x
inner join
( -- to find the students with the latest test that they attended for each group
select s.st_id, a.ted_st_id, b.te_gr_id, coalesce(max(c.te_nr), 0) te_nr
from tblStudent s
inner join tbltests b on 1 = 1 -- to get all the groups for each student
left join tbltestsdone a on s.st_id = a.ted_st_id
left join tbltests c on c.te_id = a.ted_te_id and
c.te_gr_id = b.te_gr_id
group by s.st_id, a.ted_st_id, b.te_gr_id
) y on x.te_gr_id = y.te_gr_id and
x.te_nr > y.te_nr
group by y.st_id, x.te_gr_id
having min(x.te_nr) > max(y.te_nr) -- to get the next available test for each group
order by y.st_id
October 2, 2007 at 11:41 am
Steve Jones - Editor (10/2/2007)
This almost sounds like homework. Can you post what you've tried?
This is a little more sofisticated than the normal student questions we get (like how do I use group by). This may still be homework but it seems legit to me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply