Jump to content

2 tables, 1 query


Tandem

Recommended Posts

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.