corbin Posted October 19, 2006 Share Posted October 19, 2006 Ive been tryin for the past 20 minutes or so to figure out how to somehow get mssql to run a query thata. selects amount from user_item where item_type is 57b. selects amount from user_warehouse where item_type is 57c. adds them together and treats them as colum xd. ORDER BY x DESCMy problem is i cannot figure out how to structure a sql query that does what i desire. Any help will be appreciated :P Quote Link to comment Share on other sites More sharing options...
redarrow Posted October 19, 2006 Share Posted October 19, 2006 [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] Quote Link to comment Share on other sites More sharing options...
corbin Posted October 19, 2006 Author Share Posted October 19, 2006 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... Quote Link to comment Share on other sites More sharing options...
redarrow Posted October 19, 2006 Share Posted October 19, 2006 Heres a link to help you then report back what you done to get this going ok.http://www.1keydata.com/sql/sqlandor.htmlmaybe 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] Quote Link to comment Share on other sites More sharing options...
corbin Posted October 19, 2006 Author Share Posted October 19, 2006 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 Quote Link to comment Share on other sites More sharing options...
MCP Posted October 19, 2006 Share Posted October 19, 2006 [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 thata. selects amount from user_item where item_type is 57b. selects amount from user_warehouse where item_type is 57c. adds them together and treats them as colum xd. ORDER BY x DESCMy 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_stuffgroup by item_typeorder by sum(amount) desc[/code]that should get you started Quote Link to comment Share on other sites More sharing options...
corbin Posted October 19, 2006 Author Share Posted October 19, 2006 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... Quote Link to comment Share on other sites More sharing options...
corbin Posted October 21, 2006 Author Share Posted October 21, 2006 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_idWHERE (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.ExpORDER BY total DESC[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.