Joining Tables

iceraider

SELECT gold, sum(worth)  AS sum
FROM spends
WHERE spend_date >  '2004-10-1' 
             AND donation =  '0' AND bid =  '3279272'
             AND TYPE  !=  'insurance'
             AND invested =  '0'
GROUP  BY gold
ORDER  BY sum DESC 
LIMIT 3

Ok that selects the first table with what I want.. now I have almost identical table.. but both have the same values..

I want to get the highest sum of table spends and table cycle to get the a main value.

Basically I need that query to run on two tables.. and five me 3 rows with the sum from one table added with the sum from another.

So lets say I had

Table - spends
Row 1 - worth=5.5, gold=456421
Row 2 - worth=3.3, gold=352524
Row 3 - worth=4.5, gold=522346
Row 4 - worth=2.8, gold=234234

Table - cycle
Row 1 - worth=4.5, gold=456421
Row 2 - worth=2.3, gold=352524
Row 3 - worth=9.5, gold=522346
Row 4 - worth=8.8, gold=234234

Now I want my query to return.. (ORDER BY sum DESC LIMIT 3)
Row 1- worth=14, gold=522346
Row 2 - worth=11.60, gold=234234
Row 1 - worth=11, gold=456421

tasistro

SELECT spends.worth+cycles.worth as worth, spends.gold as gold FROM cycles, spends WHERE cycles.gold=spends.gold ORDER BY worth DESC LIMIT 0,3

Should work. Haven't tried it out though.

iceraider

Can we try and use this criteria..

WHERE spend_date >  '2004-10-1'  

             AND donation =  '0' AND bid =  '3279272' 
             AND TYPE  !=  'insurance' 
             AND invested =  '0'

Also yours wouldnt work.. because THEY BOTH need 'gold' .. it could go either way.. and thats why I might have to use the JOIN syntax

iceraider

I guess every one is stumped :queasy:

ahundiak

http://dev.mysql.com/doc/mysql/en/UNION.html