Results 1 to 7 of 7

Thread: Tricky SQL

  1. #1
    Join Date
    Sep 2007
    Posts
    16

    Unanswered: Tricky SQL

    Hello,
    I have four diffrent tabels:
    bo_ Class, bo_Competition, bo_Result, bo_Licence
    And list all Licence where bo_Class.classRankingNbr >0

    ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
    ------------ --------- --------------- ------------- ----------- --------------- ----------
    M70891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
    M050887PON01 Pontus Svensson U23 SM 2007 - Herrar 2 1 H
    M181188MAR01 Marcus Edlund U23 SM 2007 - Herrar 3 1 H
    M190291JOH01 Johan Helldén U23 SM 2007 - Herrar 4 1 H
    M180360GER01 Gert Lindholm Herrar klass A 1 2 H
    M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
    K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
    K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
    K050785CAR01 Carin Johansson U23 SM 2007- Damer 3 1 D

    If bo_Class.ClassRowNbr for an Class is 1 get out place 1 from that competition,
    and if an ClassRowNbr is 2 get out top2 from that competition.
    And so on.

    From that list i want an SubQuery list where all licens order by where
    bo_Result.ResultPlace = bo_Class.ClassRowNbr.

    In text form:

    ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
    ------------ --------- --------------- ------------- ----------- --------------- ----------------------
    M170891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
    M180360GER01 Gert Lindholm Herrar klass A 1 2 H
    M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
    K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
    K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
    K180793LIN01 Linnéa Hamberg Damer Klass A 1 1 D


    Hope someone can take the time and help me out.

    Best regards
    Gert Lindholm

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't understand the assignment from your description. Is the original assignment (as given out in class) online somewhere, or can you post your teacher's email for us so we can ask them some questions?

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In any event I don't understand the question.
    Where does this line come from?
    K180793LIN01 Linnéa Hamberg Damer Klass A 1 1 D
    Gerten - please can you supply DDL and DML to produce the table and populate it? Please could you also confirm your expected results are correct? Please could you also format the expected results so the columns line up nicely (you will need to enclose then in the code tags for this).

    Thanks
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2007
    Posts
    16
    Sorry Pootle flump the first list is shorted,

    I attach an word.doc so you can se the DDL. And maybe understand what reusult i want. Sorry I can't get the DML right now.

    Best regard
    Gert
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2007
    Posts
    16

    Som error

    I come this far:
    SELECT r.ResultLicNbr, cl.ClassName, r.ResultPlace,
    cl.ClassRankingNbr, cl.ClassRankingFemaleMale
    FROM bo_Class cl
    JOIN bo_Competition co ON cl.ClassId = co.Id
    JOIN bo_Result r ON cl.ClassId = r.ResultId
    AND r.ResultRowNbr = cl.ClassRowNbr AND
    r.ResultLicNbr IN
    (SELECT bo_Result.ResultPlace FROM bo_Competition
    INNER JOIN bo_Class ON bo_Competition.Id = bo_Class.ClassId
    INNER JOIN bo_Result ON bo_Competition.Id = bo_Result.ResultId
    WHERE bo_Result.ResultPlace = bo_Class.ClassRankingNbr)
    but get following error:
    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value 'K010738ING01 ' to data type int.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Gerten
    r.ResultLicNbr IN
    (SELECT bo_Result.ResultPlace FROM bo_Competition
    ResultLicNbr is not an integer, where resultplace is, right?
    You can only compare items of the same data type.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •