open All Channels
seplocked EVE Technology Lab
blankseplocked Please help with SQL query, merging rows
 
This thread is older than 90 days and has been locked due to inactivity.


 
Author Topic

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:
	30000001	30000003
30000001 30000005
30000001 30000007
30000002 30000005
30000002 30002973

into:
	30000001	30000003	30000005	30000007
30000002 30000005 30002973 NULL

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.


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

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:

	30000001	30000003	1
30000001 30000005 2
30000001 30000007 3
30000002 30000005 1
30000002 30002973 2


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.

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

Very Happy


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.

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.

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 Very Happy

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.

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 Very Happy


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? Smile

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 Very Happy and am now mostly into Oracle again Sad

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
Intergalactic Absurdities Unlimited
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 :)


 

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only