Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0

    SQL Query- subquery? (im lost)


    hey-

    got 2 tbls, 1 tbl, tblscores, has records for all activity for each course. each course has compid's defined in tblScoreElements. a user has completed the course if all compid's have a valid record per course in the scores tbl. so i need to check if teh usr has a rec as described. help?

    tbl scores:
    usr id
    crs id
    compid
    date
    score

    tbl scorelements:
    crs id
    compid

    ex:
    course 1 has 3 compid's defined in scoreelements(eg- a,b,c). usr 1 has a score for comp id A & B in scores- so the course isnt complete.

    usr 2 has 2 'A' compid records in scores, 'c'- so again, crs not complete

    usr 3 has a record in scores for a,b & c- so usr 3 is complete- how would i retrieve this record? ive tried having an 'in' clause for scoreelements.compid- but i thinking im going the wrong way..

    thanks-
    duncan

    ps- ive been trying to do this myself for a week now- so pls help
    Last edited by dcb; September 30th, 2003 at 11:28 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
    select usrId,crsId from tblScores as t
    group by usrId,crsId
    having count(*) = (select count(*)
    from tblScorelements
     where crsId = t.crsId)
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    thanks- it seems to work- but got way fewer results that expect (146 out of 27k records)

    note- the usr can have multiple records for each component id defined in tblscoreelements

    ie- 1 usr has 7 records for i comp id in a particular crs

    i am unsure- but i think this checks if the record counts are the same. maybe i need to intsert some code to check if the values match- kinda like an array i think?

    i do appreciate your help Mr boogie
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
    select usrId,crsId from tblScores as t
    group by usrId,crsId
    having count(distinct compid) = (select count(compid)
    from tblScorelements
     where crsId = t.crsId)
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    im trying to fix it myslef- but i get a syntax err on: count(distinct compid) = (select count(compid)
    from tblScorelements
    where crsId = t.crsId)
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Could you post your exact query and error message?
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    select userlogonId,coursenumber from Scores as t
    group by userlogonId,coursenumber
    having count(distinct componentid) = (select count(componentid)
    from ScoreElements where coursenumber = t.coursenumber);
    ----------------------------------

    syntax error (missing operator) in qery expression 'count(distinct componentid) = (select count(componentid) from scoreelements where coursenumber = t.coursenumber
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    So the query is executable with using count distinct? If so it looks like a strange limitation in SQL server.
    You could try

    Code:
    select userlogonId,coursenumber 
    from Scores as t
    where 
    (select count(distinct componentid) 
      from scores 
    where userlogonid = t.userlogonid
    and coursenumber = t.coursenumber)
    = (select count(componentid)
    from ScoreElements 
    where coursenumber = t.coursenumber)
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    y- im using access 2k- so it is weak, im researching the EXISTS keyword no- this is frustrating, should be simple!

    thanks fro your help tho-
    duncan
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,149
    Rep Power
    4274
    well, golly, if you post an access problem in the sql server forum and don't even mention you're using access....

    access doesn't support COUNT DISTINCT

    so what you have to do is define a query and save it

    select crsid, count(*) as components
    from scorelements
    group by crsid

    save that as comp_query

    then

    select usrid, crsid, count(*)
    from scores
    group by usrid, crsid
    having count(*) =
    ( select components from comp_query
    where crsid = scores.crsid )

    or something very similar

    rudy
    http://r937.com/
  20. #11
  21. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    ok- thanks- learned that access really sucks
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,149
    Rep Power
    4274
    you're welcome

    and no, it doesn't

    you just happened to catch it on one of the things it doesn't do well

    let's see any other database do a crosstab query, though



  24. #13
  25. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    let me rephrase- i suck @ access.

    this isnt working- i have 27k records and this only returns 146

    heres the subquery:
    SELECT scoreElements.COURSENUMBER, Count(*) AS IDS
    FROM scoreElements
    GROUP BY scoreElements.COURSENUMBER, scoreElements.COURSETYPE
    HAVING (((scoreElements.COURSETYPE)=13));

    ok works good/correctly

    2nd query to find out who has a valid entry for each id in scorelements(works not so good)-
    SELECT Scores.USERLOGONID, Scores.COURSENUMBER, count(*)
    FROM Scores
    group by USERLOGONID, COURSENUMBER
    having count(*) =
    (select IDS from qryGetIDS
    where IDS = scores.COMPONENTID);

    let me rephrase q- 2 tbl's
    1- scores
    usr id
    compid- reference of compID in Score Elements(and has '00000' for current score & 'complete' for completion entered by usr[so not 100% accurate])
    score -# 1-100
    date- timestamp

    2- scorelements
    crsid - crs id; not key
    compid- defines name of componnent id for each crs- no set format or # per course
    crs_type- must EQ 13;

    help?
    Duncan

    i thougth this should be simple- but im striking out
  26. #14
  27. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    ps- compID in score elements are either #- 013,032,033; 141,231,351;quiz1,quiz2,quiz3;sim1,sim2,sim3

    which can vary per course/chapter- to be a 'completed' course- i would like to check if the usrid has a valild record for each compid per crsid

    (with a avg score of > 75%, but i think i can build that in by myself)
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,149
    Rep Power
    4274
    Originally posted by dcb
    ps- compID in score elements are either #- 013,032,033; 141,231,351;quiz1,quiz2,quiz3;sim1,sim2,sim3
    you have comma-delimited lists inside a column?

    that's gonna screw up your queries no matter what database you're running

    plus, i'm afraid i still do not understand your tables

    could you generate a DDL script for them please?

    and show a few sample rows

    thanks

    rudy
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo