Jump to content

Archived

This topic is now archived and is closed to further replies.

corbin

Adding mssql colums...

Recommended Posts

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

Share this post


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

Share this post


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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
[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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

×

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.