Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unhappy Unanswered: Question on SQL experission for complicated query

    Hello All,

    I have a problem on how to retrieve records from a database using SQL query. It is a little bit complicated query. I do not know what SQL query experission is correct.


    In the database table, there are four fields "ClassID", "Personal Nmae", "Age", "Score". The following is an example for illustration.

    Class Name Age Score
    0 Bob 20 78 <====
    0 John 26 66 <****
    2 Wilson 28 88
    1 John 26 77 <****
    3 Alice 25 56
    1 Bob 20 89 <====


    In the above example table, I would like to know the scores of students who share the same name, at the same age, BUT in different class (ClassID is specified by the user). Actually, I need a SQL SELECT experission which will lead to a query result (a recordset) like the following


    Bob 78 89
    John 66 77

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select T.name
         , T.score
      from daTable as T
    inner
      join (
           select name
                , age
             from daTable
           group
               by name
                , age
           having count(*) > 1
           ) as D
        on D.name = T.name
       and D.age  = T.age
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    r937,

    Thank you for your reply, i will try your code and report latter.

  4. #4
    Join Date
    Apr 2007
    Posts
    130
    yes, it is correct. However, what is the SQL experission like if we use 'self join' to implement the same thing.

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Curiously is there anything wrong with doing the following?

    Code:
    SELECT s1.Name,s2.Score
    FROM student_results s1
    JOIN student_results s2 
    ON s1.Name=s2.Name 
    AND s1.Age=s2.Age 
    AND s1.Class<>s2.Class

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not "wrong" per se, but what if a given name has three scores -- what does your query return? what does mine?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2007
    Posts
    130
    Thanks aschk abd r937,

    Actually, in my application there are only two scores for a given name. One more further question,

    If we have another table 'ClassTotalScore' consisting of two fields "Class" , "TotalScore"

    Can I add some clause to the above suggested SQL experissions to get the sum of total scores of Class=0 and Class=2. Or I have to use another SQL statement to get the sum of total scores of two classes.

    Moreover, if either way is possible, I would like to know whether ONE SQL statement is necessarilyfaster than TWO SQL statements.


    Actually, those data in the two tables originally are stored in varibles in my VC++ 6.0 program. However, as more and more data are produced, the PC memory is almost exhaused. Hence, i have to resort to the database technology. I notice that the running speed of the databased based program is 10 times slower than the original program. I wonder if this is normal.

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Sounds like your database abstraction layer might be doing something odd (either that or your database system is setup badly/running slowly).
    I would still recommend rudy's method of SQL Query by the way. I haven't tested mine with more than two results (because i deleted the tables) but from experience I know that rudy tends to be right

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Unless your programme is using some really nasty inefficient way of storing all your SQL results???

  10. #10
    Join Date
    Apr 2007
    Posts
    130
    aschk,


    thanks.

    I think i did not express my problem clearly in the previous posts. Actually, the speed of storing data (write) is ok for me. I am compliant with the speed of retrieving data (read data)--- more than 10 times slower than the memory based method.


    aschk, are you sure this is not normal.

    BY the way, there are 1200 plus records in the database.


    10 times slower than the original program

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Is the database on the same machine as the program? Is your database cache limit really low? 1200 is nothing really. If you said 12 Million records then I would question it. If your database is on a seperate machine and the records are big then transferring all that data across a network might be your slowdown point. Where does your program take the longest?

  12. #12
    Join Date
    Apr 2007
    Posts
    130
    aschk,


    thanks.



    Yes, I did the tests (both reading--retrieving data and writing--storing data) in the same program on a DELL Optiplex GX620 (3Ghz CPU), 2G memory.


    I am new to SQL server 2000, where is the setting for database cache?

  13. #13
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What else is your server doing? Because for it to write quicker than it reads is definitely odd. If you run the query directly in Query analyser how long does it take?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    write = 1 row

    read = all 1200 rows

    of course it will be slower

    cy, what indexes have you crated?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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