2 tables, 1 query

[Tandem]

Hi. I'm trying to use records from two tables to try to make a sort of score board. I'll try to explain the situation as thoroughly as possible.

Table1 has the following fields: Username, Number1
Table 2 has the following fields: Username, Number2

Every user has an entry into both tables. I want to add up Number1 and Number2 for every user, and then order in descending order, so i can make the score board from this information

How would i go about doing that?

Any help appreciated.

[Barand]

Try
[code]
SELECT a.username , (a.number1 + b.number2) as total
FROM table1 a INNER JOIN table2 b ON a.username = b.username
ORDER BY total DESC[/code]

[Tandem]

Thanks for the reply.

What if the situation changed. Let's say that not all players had records in Table 2, but i wanted the results to show who has the total number.

So for example if somebody had no record in table 2 nut his 'Number 1' was higher than anyone elses, and was higher than anyone elses Number 1 and Number 2 combined, they would be at the top.

Please say if this doesn't make sense.

[Barand]

Change INNER JOIN to LEFT JOIN

[Tandem]

Again, thanks for the reply.

When i change it to LEFT JOIN i still get the same result.

Let me try to explain again, as i missed a few words and made a few typos in my last post.
I'm just going to say it how it is instead of theoretical :P

This is for a game where players can earn 'cash'. I have two tables, one called MEMBERS, and one called BANK. Every member has a record in MEMBERS. One of the fields in MEMBERS is CASH, which is how much 'cash' they have. Members can choose to put their 'cash' in the 'bank', and upon doing so will have a record added to the table BANK. The field from this table where there 'cash' is stored is called BANKED.

I want to create a score board showing who has the most total cash in descending order. So for the players who have records in BANK, there total would be CASH + BANKED, and everyone elses total is just CASH.

I hope this makes sense.

[Barand]

try
[code]<?php
$sql = "SELECT m.username ,
    CASE
        WHEN b.banked IS NULL THEN m.cash
        ELSE (m.cash + b.banked)
    END as total
    FROM member m LEFT JOIN bank b ON m.username = b.username
    ORDER BY total DESC";

$res = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_assoc($res)) {
    echo $row['username'], ' ', $row['total'], '<br/>';
}
?>[/code]