---help please -totally confused

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)

---help please -totally confused

Author

Topic

mjquinno
Starting Member

6 Posts

Posted - 2004-06-25 : 12:45:16

hi guys im in a world of doo doo i think ok her it is ...

I have this sql statement

SELECT courseInstance, score, userId
FROM userCourses
WHERE (SUBSTRING(courseInstance, 0, 11) IN
(SELECT SUBSTRING(courseInstance, 0, 11)
FROM userCourses
WHERE courseid = 'PRE002' AND score < 80 AND status IN 'C', 'F'))) AND (courseId = 'PRE002')
ORDER BY userId

This returns

PRE00275931 30 7593
PRE00278471 77 7847
PRE00279231 43 7923
PRE00279232 43 7923
PRE00279301 0 7930
PRE00279302 87 7930
PRE00279303 0 7930
PRE00280141 7 8014
PRE00280142 7 8014
PRE00280143 7 8014
PRE00280144 7 8014
PRE00280145 7 8014
PRE00280146 7 8014
PRE00281471 60 8147
PRE00281472 90 8147
PRE00281521 43 8152

the courseinstance is made up of the userid
So this user took the course twice failed the first time and passed the second.

PRE00281471 60 8147
PRE00281472 90 8147

Ok now i need to run through this returned list and return only 1 score for each user but it still needs to check that the score is under 80.

any help much appriated..
Thanks for looking

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-25 : 13:05:46

Something like this might do it:

select min(a.score) as score, a.userid
from
(SELECT courseInstance, score, userId
FROM userCourses
WHERE (SUBSTRING(courseInstance, 0, 11) IN
(SELECT SUBSTRING(courseInstance, 0, 11)
FROM userCourses
WHERE courseid = 'PRE002' AND score < 80 AND status IN 'C', 'F'))) AND (courseId = 'PRE002')
ORDER BY userId) as a
group by a.userid
having min(a.score) < 80

Hopefully it works :)

Duane.

mjquinno
Starting Member

6 Posts

Posted - 2004-06-28 : 07:20:08

Thanks very much mate that working a dream thing is i cant get it to return all the rows in the table that those values returned. Any ideas

Thanks again

D

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-28 : 07:28:28

Send some example data and what is being returned and what should be returned, maybe then we can help.

Duane.

mjquinno
Starting Member

6 Posts

Posted - 2004-06-28 : 07:31:06

(select max(a.score) as score, courseInstance from (SELECT * FROM userCourses WHERE (SUBSTRING(courseInstance, 0, 11) IN (SELECT SUBSTRING(courseInstance, 0, 11) FROM userCourses WHERE courseid = 'PRE002' AND score < 80 AND status IN ('C', 'F'))) AND (courseId = 'PRE002') ) as a group by courseinstance having max (a.score) < 80)

returns

PRE00275931 77
PRE00278471 43
PRE00279231 43
PRE00279232 0
PRE00279301 0
PRE00279303 7
PRE00280141 7
PRE00280142 7
PRE00280143 7
PRE00280144 7
PRE00280145 7
PRE00280146 60
PRE00281471 43
PRE00281521 73
PRE00281651 73
PRE00281652 63
PRE00281821 40
PRE00281861 40
PRE00281862 40
PRE00281863 40
PRE00281864 40
PRE00281865 43
PRE00281911 73
PRE00281912 43
PRE00282071 43
PRE00282072 37
PRE00282151 37
PRE00282152 37
PRE00282153 37
PRE00282154 37
PRE00282155 53
PRE00282161 42
PRE00282162 30
PRE00282221 30
PRE00282222 0
PRE00282291 17
PRE00282361 67
PRE00282362 53
PRE00282371 10
PRE00282391 10
PRE00282401 73
PRE00282402 30
PRE00282403 30
PRE00282404 77
PRE00282405 37
PRE00282481 0
PRE00282621 57
PRE00282622 67
PRE00282651 78
PRE00282652 0
PRE00282701 23

exactly what i wanted but i need the entire row of data not just the courseinstance and score from the usercources table

mjquinno
Starting Member

6 Posts

Posted - 2004-06-28 : 07:42:12

my apologies the last post was from a while ago testing ..

select max(a.score) as score, a.userId from (SELECT courseInstance, score, userId FROM userCourses WHERE (SUBSTRING(courseInstance, 0, 11) IN (SELECT SUBSTRING(courseInstance, 0, 11) FROM userCourses WHERE courseid = 'PRE002' AND score < 80 AND status IN ('C', 'F'))) AND (courseId = 'PRE002') ) as a group by a.userid having max (a.score) < 80

7593 77
7847 43
7923 7
8014 43
8152 73
8165 63
8182 40
8186 73
8191 43
8207 37
8215 53
8216 30
8222 0
8229 67
8236 53
8237 10
8239 77
8240 57
8262 78
8265 23
8270 10
8274 70
9191 33

i want to get the entire row of data back from usercourse only for where they have the above scores.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-28 : 07:47:26

Does this do it?

select c.*
from UserCources c
join
(select max(a.score) as score, a.userId from (SELECT courseInstance, score, userId FROM userCourses WHERE (SUBSTRING(courseInstance, 0, 11) IN (SELECT SUBSTRING(courseInstance, 0, 11) FROM userCourses WHERE courseid = 'PRE002' AND score < 80 AND status IN ('C', 'F'))) AND (courseId = 'PRE002') ) as a group by a.userid having max (a.score) < 80) as b
on b.courseinstance = c.courseinstance
and b.score = c.score

Duane.

mjquinno
Starting Member

6 Posts

Posted - 2004-06-28 : 07:55:17

Yes it returns the entire table but if the user has a few entries all failing im getting them all back i just want the highest below 80

courseinstace course userid score
PRE00295962 PRE002 9596 33
PRE00295963 PRE002 9596 13
PRE00295964 PRE002 9596 27

i only want toget the line with the 33 score back the rest i dont need. Any ideas.

Man thanks for the help i would be lost without you thanks again.
D

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-28 : 08:03:30

Sorry,
I made a typo.

Does this work?

select c.*
from UserCources c
join
(select max(a.score) as score, a.userId from (SELECT courseInstance, score, userId FROM userCourses WHERE (SUBSTRING(courseInstance, 0, 11) IN (SELECT SUBSTRING(courseInstance, 0, 11) FROM userCourses WHERE courseid = 'PRE002' AND score < 80 AND status IN ('C', 'F'))) AND (courseId = 'PRE002') ) as a group by a.userid having max (a.score) < 80) as b
on b.userid = c.userid
and b.score = c.score

Duane.

mjquinno
Starting Member

6 Posts

Posted - 2004-06-28 : 08:10:30

hi duane ya thing is im still having the problem with the returning values i only want the one line for each userID. maybe a disinct cause if i have multiple instances with the same score it outputs them all.
instance course userid score
PRE00280141 PRE002 8014 7
PRE00280142 PRE002 8014 7
PRE00280143 PRE002 8014 7
PRE00280144 PRE002 8014 7

Thanks again

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources