EVE Search - Please help with SQL query, merging rows

All Channels

EVE Technology Lab

Please help with SQL query, merging rows

» Click here to find additional results for this topic using Google

Monitor this thread via RSS [?]

Pages: [1] :: one page
Author	Thread Statistics \| Show CCP posts - 0 post(s)
g'Ar-Mak Kafshaz	Posted - 2011.06.10 14:04:00 - [1] I've quite new to SQL so any assistance is greatly appreciated. What I want to do is create a new table of solar system connections. create table jumptable (fromSystem int NOT NULL, toSystem1 int, toSystem2 int, ... toSystem9 int) (I'm not sure of the greatest number of connections a system has, but I know I'll find out while doing this.) I know I need to get the rows from dbo.mapSolarSystemJumps. What I don't know is how to merge the rows from: 3000000130000003 3000000130000005 3000000130000007 3000000230000005 3000000230002973 into: 30000001300000033000000530000007 300000023000000530002973NULL Thank you in advance for any help.
Max Kolonko Caldari Worm Nation Ash Alliance	Posted - 2011.06.10 14:40:00 - [2] I'm afraid You are doing it wrong. Altough it may be possible with sql procedures, you can't do it with plain SQL and even with a procedure it will be pain in the *** to do so. Maybe You can say what is the result you are trying to acomplish that needs data in this specific format, meybe i can propose different approach. Max Kolonko
g'Ar-Mak Kafshaz	Posted - 2011.06.10 14:57:00 - [3] Originally by: Max Kolonko Maybe You can say what is the result you are trying to acomplish that needs data in this specific format, meybe i can propose different approach. I want a list that contains every solar system and all of the connections from that system. I will then be able to query specific systems to get data about that system and it's neighbors. In other words, this new table will be part of a much bigger query. The idea that sparked this was to be able to get the list of agents in a small area... i.e. Eve-Agents with a range filter. This new table has uses well beyond that idea though.
Max Kolonko Caldari Worm Nation Ash Alliance	Posted - 2011.06.10 15:42:00 - [4] sooo, why not just use already avaible table? 30000001 30000003 30000001 30000005 30000001 30000007 30000002 30000005 30000002 30002973 I still dont understand why result have to be in one row? You just need "select * from tablename where systemid=xxx" and get few rows containing only info for that specific system. for example 3 forws for id= 30000001 30000001 30000003 30000001 30000005 30000001 30000007 SQL is not a good tool to work if you want the same data in different columns, because when you create joins in query you have to give specific colum on wich you will be making connection between table Max Kolonko
Xzorxur	Posted - 2011.06.10 15:56:00 - [5] The previous replies are correct. I just wanted to addd that subquerries might help you. You can do a query like: SELECT jumptable.toSystem FROM jumptable, (SELECT jumptable.toSystem as system FROM jumptable WHERE jumptable.fromSystem = x) AS t1 WHERE t1.toSystem = jumptable.fromSystem; That should give you all the within 2 jumps of system x. I hope I didn't mangle that example too bad, I'm on the train right now so can't test. I'm sure you can Google some good examples though.
Matthew Caldari BloodStar Technologies	Posted - 2011.06.10 17:24:00 - [6] While I agree with the above posters that it's a really bad idea in the first place, there might be a way to achieve this if you are using a current version of SQL Server. You'd have to use ROW_NUMBER partitioned over the fromSystem to derive a toSystemNumber field. So you'd get: 30000001300000031 30000001300000052 30000001300000073 30000002300000051 30000002300029732 Then you'd use PIVOT to convert the values of toSystemNumber into your toSystem fields. But I really would recommend finding a way to do whatever it is you want to do in the original table structure. ------- There is no magic Wand of Fixing, and it is not powered by forum whines.
Cpt Placeholder	Posted - 2011.06.10 18:56:00 - [7] Edited by: Cpt Placeholder on 10/06/2011 18:57:43 +1 for bad idea. But if you want to go ahead and throw yourself into that hell, it would be much easier (at least for me) to generate the SQL script with an actual programming language than do that with some non-portable SQL extension.
Lutz Major	Posted - 2011.06.10 20:11:00 - [8] Originally by: g'Ar-Mak Kafshaz (I'm not sure of the greatest number of connections a system has, but I know I'll find out while doing this.) SELECT fromSolarSystemID, COUNT() FROM mapSolarSystemJumps GROUP BY fromSolarSystemID ORDER BY 2 DESC The above query reveals that there are systems with up to eight gates. And here is the standard* SQL query to fill your table (I won't format it, because it will break the forum): SELECT s.solarSystemID fromSystemID, s1.solarSystemID toSystem1, s2.solarSystemID toSystem2, s3.solarSystemID toSystem3, s4.solarSystemID toSystem4, s5.solarSystemID toSystem5, s6.solarSystemID toSystem6, s7.solarSystemID toSystem7, s8.solarSystemID toSystem8 FROM mapSolarSystems s LEFT JOIN mapSolarSystems s1 ON s1.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s2 ON s2.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s1.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s3 ON s3.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s2.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s4 ON s4.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s3.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s5 ON s5.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s4.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s6 ON s6.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s5.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s7 ON s7.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s6.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s8 ON s8.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s7.solarSystemID GROUP BY toSolarSystemID) WHERE s1.solarSystemID IS NOT NULL I also want to agree with the above posters, that this is indeed a bad idea to create such a table.
EveOrg	Posted - 2011.06.10 21:02:00 - [9] Edited by: EveOrg on 10/06/2011 21:02:15 That is a disgusting query, please take it away. OP learn to use RDBMS properly.
Tonto Auri Vhero' Multipurpose Corp	Posted - 2011.06.10 21:49:00 - [10] In sense of self-learning it isn't a bad idea to try and create such a table. But in sense of proper use of RDBMS I see it least desirable to wreak this kind of havok on the database. Before you go ahead and write any query, please stop for a second and state clearly, what is the result you want to achieve with it, and how much time you would save for the database when using that table instead of one already present. -- Thanks CCP for cu
Max Kolonko Caldari Worm Nation Ash Alliance	Posted - 2011.06.11 00:26:00 - [11] Originally by: Lutz Major SELECT s.solarSystemID fromSystemID, s1.solarSystemID toSystem1, s2.solarSystemID toSystem2, s3.solarSystemID toSystem3, s4.solarSystemID toSystem4, s5.solarSystemID toSystem5, s6.solarSystemID toSystem6, s7.solarSystemID toSystem7, s8.solarSystemID toSystem8 FROM mapSolarSystems s LEFT JOIN mapSolarSystems s1 ON s1.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s2 ON s2.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s1.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s3 ON s3.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s2.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s4 ON s4.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s3.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s5 ON s5.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s4.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s6 ON s6.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s5.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s7 ON s7.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s6.solarSystemID GROUP BY toSolarSystemID) LEFT JOIN mapSolarSystems s8 ON s8.solarSystemID = (SELECT MIN(toSolarSystemID) FROM mapSolarSystemJumps WHERE fromSolarSystemID = s.solarSystemID AND toSolarSystemID > s7.solarSystemID GROUP BY toSolarSystemID) WHERE s1.solarSystemID IS NOT NULL Nice one, havent thought of it that way. It looks like this one will work. Max Kolonko
Lutz Major	Posted - 2011.06.11 17:24:00 - [12] Originally by: EveOrg That is a disgusting query, please take it away. LOL! If this one offends you already, you haven't really used SQL yet
Zeta Zhul Caldari Preemptive Paranoia	Posted - 2011.06.11 23:38:00 - [13] Basically you've got two choices: elegant or crappy. 1. Oracle or Personal Oracle gives you the CONNECT BY PRIOR clause in a Select statement to make a recursive Select. 2. MySQL gives you nothing but if you google 'mysql recursive select' or include 'hierarchial' then you should get where you need to be. Generally a stored procedure that will store each level of the result and then call itself recursively until it reaches the depth, level or number of jumps you want to search. 3. MSSQL has 'hierarchialid' and might have some new addition for hierarchial queries. Otherwise you're doing the recursive stored procedure thing. 4. It helps a lot if you include the specific SQL engine you are using as there are substantial differences in how to do things.
Matthew Caldari BloodStar Technologies	Posted - 2011.06.12 11:55:00 - [14] Originally by: Zeta Zhul 3. MSSQL has 'hierarchialid' and might have some new addition for hierarchial queries. Otherwise you're doing the recursive stored procedure thing. If you want to do recursive queries on MSSQL, you could also look into a recursive Common Table Expression. Some of the examples on BOL look like they would apply fairly directly to the problem of outputting a jump heirarchy from mapSolarSystemJumps. ------- There is no magic Wand of Fixing, and it is not powered by forum whines.
EveOrg	Posted - 2011.06.12 14:33:00 - [15] Originally by: Lutz Major Originally by: EveOrg That is a disgusting query, please take it away. LOL! If this one offends you already, you haven't really used SQL yet LOL! Yet? I've been a MS SQL DBA and developer since v4 (circa 1993); yes, I am old :) I was suggesting, in a rubbish way, that trying to flatten multiple results into a single row - while possible - is usually indicative that whatever code consumes it is inefficient. Nor does it scale, that query will only work for systems with up to 8 gates. Are you absolutely sure the code that consumes it isn't utterly bonkers?
Lutz Major	Posted - 2011.06.12 15:28:00 - [16] Originally by: EveOrg yes, I am old :) Indeed, you are :) I started in 1996 with Oracle, switched to SQL Server in 2000 and am now mostly into Oracle again Anyway I just wanted to show, that a SQL query for this task is possible. And I concur (and repeat) that it is indeed a bad idea creating such a jumptable. Back in the 90's I might have done it similar (when having heavy load on the jump table!) - due to limited db resources and bad locking algorithms - but today ....
g'Ar-Mak Kafshaz	Posted - 2011.06.12 19:55:00 - [17] @Lutz ... Thank you for the code, it will at least give me something to fiddle with. @All ... I'm extremely new to SQL; started poking around with it just for EVE. My DB experience pretty much ended in the very early '90s. I'm not trying to create the latest and greatest tool, just trying to find a new system to call home. Thank you for any positive constructive advice/comments that you have provided. For those that asked: MS SQL Server Management Studio 2008 (10.0.4000.0)
OnoSendai Caldari Tolerance Training Academy	Posted - 2011.06.12 21:21:00 - [18] I find this intriguing and think I understand what he is trying to do. How would you query the existing data for a list of systems within X jumps of Y system?
Lutz Major	Posted - 2011.06.13 06:56:00 - [19] Originally by: OnoSendai I find this intriguing and think I understand what he is trying to do. How would you query the existing data for a list of systems within X jumps of Y system? Recursion is better done on the existing mapSolarSystemJumps table than on the other one :)

Pages: [1] :: one page
First page \| Previous page \| Next page \| Last page

COPYRIGHT NOTICE
EVE Online, the EVE logo, EVE and all associated logos and designs are the intellectual property of CCP hf. All artwork, screenshots, characters, vehicles, storylines, world facts or other recognizable features of the intellectual property relating to these trademarks are likewise the intellectual property of CCP hf. EVE Online and the EVE logo are the registered trademarks of CCP hf. All rights are reserved worldwide. All other trademarks are the property of their respective owners. CCP hf. has granted permission to EVE-Search.com to use EVE Online and all associated logos and designs for promotional and information purposes on its website but does not endorse, and is not in any way affiliated with, EVE-Search.com. CCP is in no way responsible for the content on or functioning of this website, nor can it be liable for any damage arising from the use of this website.