comparing values in multiple tables

[Anon]

Okey allow me to explain what I am trying to do here. I am trying to make a comparison with two different tables. store and accounts are the table names, there is a field in store called cost which i am trying to make sure in this query is greater then the field money in table accounts. if the comparison returns a row then i know it is true. I don't belive i should join these tables because none of the data corresponds with each other at all, i just need to be able to make cross table field comparisions. so if cost is 500 in store and money is 600 in accounts of the row in question then the query should return atleast one row.

$query = "SELECT store.itemname, store.cost, store.iteminfo FROM store, accounts WHERE store.cost < accounts.money AND itemid = '$changeitem AND username= $usernamecook";
$result = mysql_query($query);
$numR = mysql_num_rows($result);

Also another question i have is for doing updates to a table with the same theory in mind, could i make calculations based on values from 2 seperate tables??

$resultt = mysql_query("UPDATE accounts SET accounts.money=accounts.money-store.cost WHERE username='$loginusercook'", $Connect);

thanks ya'll for taking the time to look at my problem!!

[bretticus]

Join the tables on the condition that cost is less (or equal to I assume) the money in accounts:

SELECT s.itemname, s.cost, s.iteminfo FROM store AS s INNER JOIN accounts AS a ON s.cost < a.money...

You only get a result when cost is less than money.

Hope that helps and I understood what you're trying to do.

[bretticus]

Whoops...I meant (using the gt or eq suggestion):

SELECT s.itemname, s.cost, s.iteminfo FROM store AS s INNER JOIN accounts AS a ON s.cost <= a.money...

Not a big change ;-)