Jump to content


Photo

Adding mssql colums...


  • Please log in to reply
7 replies to this topic

#1 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 19 October 2006 - 01:36 AM

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

Why doesn't anyone ever say hi, hey, or whad up world?

#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 19 October 2006 - 01:52 AM

<?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'];

}
 }
?>

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 19 October 2006 - 01:58 AM

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...
Why doesn't anyone ever say hi, hey, or whad up world?

#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 19 October 2006 - 02:20 AM

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

http://www.1keydata....l/sqlandor.html


maybe somthink like this

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

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 19 October 2006 - 02:39 AM

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
Why doesn't anyone ever say hi, hey, or whad up world?

#6 MCP

MCP
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 19 October 2006 - 03:50 AM

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

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
that should get you started

#7 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 19 October 2006 - 04:49 AM

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

@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");

Hehe forgot about my post on here...
Why doesn't anyone ever say hi, hey, or whad up world?

#8 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 21 October 2006 - 04:40 AM

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...

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

Why doesn't anyone ever say hi, hey, or whad up world?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users