Not sure if this will work with MySQL (office uses a different database and
I can't get to real one to test it) but this seems to work:

select t1.name, tot=(t2.col1+t2.col2+t2.col3+t2.col4)
from table1 t1, table2 t2, table3 t3
where t1.id1 = t3.id1
and t2.id2 = t3.id2
order by tot desc

 name  tot
 ----- -----------
 John           19
 Fred           17
 Bill           16

Looks like your totals are wrong!

You don't explain your application but wouldn't it be better to dispense with table3 
and just have table2 as:

create table table2
(
      id1 int,
      id2 int,
      rowlocation int auto_increment,
      result int
)

table2
id1   id2   rowlocation result
1     1     1           1
1     1     2           4
1     1     3           5
1     1     4           6
2     2     5           3
2     2     6           2
2     2     7           8
2     2     8           6
3     3     9           4
3     3     10          9
3     3     11          2
3     3     12          2

This would simplify your select and reduce the join to two tables. Just a thought.

Please let me know if this doesn't work. I'll try it later with a proper (i.e. MySQL) 
database.

Keith




Extranet
[EMAIL PROTECTED] - 08/02/2003 04:47

To:    mysql

cc:


Subject:    Help, Not sure my other email got through!


Not sure my other email got through, so here it is again, with an updated
description.

Hi,

 I have a database with 3 names in it. In each of these names is 5
categories  that have numbers in them. I want to be able to do a select and
total these numbers for each name, and then sort them in descending order.
I
have tried for over 6 hours tonight and no matter what I do, there is one
minor glitch. I am not supplying any code as I would like to know from you
people what the best way to go about this would be - as what I have done is
obviously not working.

 TIA

 i.e

This is what is in the database.

Table 1

ID1 NAME

1  Bill
2  John
3  Fred

Table 2

ID2  Col1 Col2 Col3 Col4

1   1    4    5    6
2   3    2    8    6
3   4    9    2    2

Database 3 is a reference file that ties them together - so I would have to
use a join in the select statement.

ID1  ID2
1        1
2        2
3        3

This is what I want to be able to show:

John   21
Fred   20
Bill     16





























sql, query, queries, smallint



---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php








This message and any attachments (the "message") is
intended solely for the addressees and is confidential. 
If you receive this message in error, please delete it and 
immediately notify the sender. Any use not in accord with 
its purpose, any dissemination or disclosure, either whole 
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message. 
BNP PARIBAS (and its subsidiaries) shall (will) not 
therefore be liable for the message if modified. 

                ---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le 
"message") sont etablis a l'intention exclusive de ses 
destinataires et sont confidentiels. Si vous recevez ce 
message par erreur, merci de le detruire et d'en avertir 
immediatement l'expediteur. Toute utilisation de ce 
message non conforme a sa destination, toute diffusion 
ou toute publication, totale ou partielle, est interdite, sauf 
autorisation expresse. L'internet ne permettant pas 
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce 
message, dans l'hypothese ou il aurait ete modifie.


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to