Adding mssql colums...

[corbin]

Ive been tryin for the past 20 minutes or so to figure out how to somehow get mssql to run a query that
a. selects amount from user_item where item_type is 57
b. selects amount from user_warehouse where item_type is 57
c. adds them together and treats them as colum x
d.  ORDER BY x DESC

My problem is i cannot figure out how to structure a sql query that does what i desire.

Any help will be appreciated :P

[redarrow]

[code]
<?php

$query1="SELECT * FROM user_item WHERE item_type='57'";
$result1=mysql_query($query1);
while(mysql_fetch_assoc($result1){


$query2="SELECT * FROM user_warehouse WHERE item_type='".$result1['item_type']."'";
$result2=mysql_query($query2);
while(mysql_fetch_assoc($result2){


echo $result2['what_ever'];

}
}
?>
[/code]

[corbin]

you realize that wont echo anything right?  And I think I made my first post ambigously...

I need to add field.table1 to field.table2 then sort the results by field.table1 + field.table2... I cant pull the results then add them them sort them since its over 1000 users...

[redarrow]

Heres a link to help you then report back what you done to get this going ok.

http://www.1keydata.com/sql/sqlandor.html


maybe somthink like this

[code]
<?php
$query=SELECT * FROM user_item a, user_warehouse b WHERE a.item_type = b.item_type AND b.item_name = '57'"
?>
[/code]

[corbin]

That doesnt help at all O.o... Maybe I'm still not explaining correctly, or what Im tryin to do is impossible...

I want to add a column in table A to a column in table B then sort that desc

[MCP]

[quote author=corbin link=topic=111971.msg454102#msg454102 date=1161221819]
Ive been tryin for the past 20 minutes or so to figure out how to somehow get mssql to run a query that
a. selects amount from user_item where item_type is 57
b. selects amount from user_warehouse where item_type is 57
c. adds them together and treats them as colum x
d.  ORDER BY x DESC

My problem is i cannot figure out how to structure a sql query that does what i desire.

Any help will be appreciated :P

[/quote]
[code]
select item_type, sum(amount) as x from (
  select item_type, amount from user_item
  union all
  select item_type, amount from user_warehouse
) user_stuff
group by item_type
order by sum(amount) desc
[/code]
that should get you started

[corbin]

Hmmm that might have worked too... But what i finally came up with was:

[code=php:0]
@mssql_query("CREATE VIEW adena_total AS SELECT user_data.builder,user_data.char_id,user_item.amount AS invamount, user_warehouse.amount AS whamount, user_data.char_name AS name FROM user_item,user_warehouse,user_data WHERE user_item.item_type = '57' AND user_data.builder = '0'");
$q = mssql_query("SELECT top 10 invamount + whamount AS total_adena, name FROM adena_total ORDER BY total_adena DESC");
[/code]

Hehe forgot about my post on here...

[corbin]

DIdnt take me long to figure out that doesnt return what im wanting... Its very unlikely, but incase someone else ever has a similar situation ill post what I ended up with...

[code=php:0]
SELECT    TOP 10 user_data.char_id, SUM(user_item.amount) AS total, user_data.char_name, user_data.gender, user_data.Lev, user_data.Exp, user_data.class, user_data.PK,  user_data.Duel FROM user_data INNER JOIN user_item ON user_data.char_id = user_item.char_id
WHERE (user_item.item_type = '57') AND (user_data.builder = '0')
GROUP BY user_data.char_id, user_data.char_name, user_data.gender, user_data.class, user_data.PK, user_data.Duel, user_data.Lev, user_data.Exp
ORDER BY total DESC
[/code]