Jump to content


Photo

2 tables, 1 query


  • Please log in to reply
5 replies to this topic

#1 Tandem

Tandem
  • Members
  • PipPipPip
  • Advanced Member
  • 251 posts

Posted 30 August 2006 - 10:45 PM

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.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 30 August 2006 - 10:59 PM

Try
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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Tandem

Tandem
  • Members
  • PipPipPip
  • Advanced Member
  • 251 posts

Posted 02 September 2006 - 07:28 PM

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.

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 02 September 2006 - 07:30 PM

Change INNER JOIN to LEFT JOIN
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 Tandem

Tandem
  • Members
  • PipPipPip
  • Advanced Member
  • 251 posts

Posted 02 September 2006 - 08:23 PM

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.

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 02 September 2006 - 10:52 PM

try
<?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/>';
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users