Jump to content

Adding mssql colums...


corbin

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.