Question on SQL experission for complicated query

cy163 · #1 (**permalink**) 04-18-07, 01:28

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

r937 · #2 (**permalink**) 04-18-07, 04:07

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

cy163 · #3 (**permalink**) 04-18-07, 06:45

r937,

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

cy163 · #4 (**permalink**) 04-18-07, 09:47

yes, it is correct. However, what is the SQL experission like if we use 'self join' to implement the same thing.

aschk · #5 (**permalink**) 04-19-07, 04:41

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

r937 · #6 (**permalink**) 04-19-07, 06:35

not "wrong" per se, but what if a given name has three scores -- what does your query return? what does mine?

cy163 · #7 (**permalink**) 04-19-07, 08:17

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.

aschk · #8 (**permalink**) 04-20-07, 06:47

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

aschk · #9 (**permalink**) 04-20-07, 09:33

Unless your programme is using some really nasty inefficient way of storing all your SQL results???

cy163 · #10 (**permalink**) 04-23-07, 08:42

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

aschk · #11 (**permalink**) 04-23-07, 08:58

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?

cy163 · #12 (**permalink**) 04-23-07, 10:25

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?

aschk · #13 (**permalink**) 04-24-07, 06:36

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?

r937 · #14 (**permalink**) 04-24-07, 07:18

write = 1 row

read = all 1200 rows

of course it will be slower

cy, what indexes have you crated?

Internet Services

Web Development

Digital Marketing

Consumer Tech