I was wondering if there was some information somewhere as to what tables are linked to other tables, albeit loosely in that no foreign keys are actually setup.
Basically am just wondering what tables can be joined together with an inner join for example, or even just queried as two separate queries.
For example if I wanted to get the following information from the final query what tables would I query to get to that final step.
Query all races, and randomly select one.
Query all bloodlines possible for the selected race above.
Query all ancestries possibly for the selected race and bloodline above.
Query all education (starter corps) options for the selected race and bloodline above.
Get the starting region, consellation, solar system for the options selected above.
I’ve been able to do 1 and 2 above, but 3, 4, and 5 have proven difficult despite spending hours trying to do it.
I’m not using a database mapping tool, or looking to add foreign keys to my database, only looking for the relationship information to help me with writing queries.
Technically there is no SQL SDE database tables. Steve’s conversions originate from YAML files which he converts to the SQL database format.
As such you would have to define such relations yourself. With some work it shouldn’t be too hard to figure out, but can get messy in certain parts for sure.
EDIT: From what i can tell from quickly going thru the SDE files Race -> Bloodline -> Ancestry Also maybe crpNPCCorporations for system of each NPC corp?
TBH it might be easier to just lookup the starting systems of each each race/starting corp and make your own table or something.
Oh yes I realise and already knew that, I wasn’t looking to create any relationships myself in the actual database.
Oh I wasn’t looking to create any relationships myself, was merely wanting to note which tables use fields that also exist in other tables, and note this done so that it would help me in creating queries later on.
When creating a character using the eve client, ancestry is able to be choosen at the same time as the education / starting corp.
Also the crpNPCCorporations table only has a solarSystemID and nothing deeper than this, so unsure how I would get down to region level by starting with each NPC corp as you suggest.
Well you would take the solarSystemID and look it up in the mapSolarSystems table to get regionID then look that up in mapRegions table to get the region name.
I tried what you said, but this only gets to me “The Citadel” but I need to get to “Todaki VI - Asteroid Belt 12”
I used the queries below, sorry for the wall of text.
SELECT * FROM [EveStaticData].[dbo].[chrRaces] WHERE [raceID] = 1;
SELECT * FROM [EveStaticData].[dbo].[chrBloodlines] WHERE [raceID] = 1;
SELECT * FROM [EveStaticData].[dbo].[chrAncestries] WHERE [bloodlineID] = 11;
SELECT * FROM [EveStaticData].[dbo].[crpNPCCorporations] WHERE [corporationID] = 1000014;
SELECT * FROM [EveStaticData].[dbo].[mapSolarSystems] WHERE [solarSystemID] = 30002805
SELECT * FROM [EveStaticData].[dbo].[mapRegions] WHERE [regionID] = 10000033;
Also I don’t know how to get from the bloodline to the correct education starting corp options, as a new character only starts in Todaki if they chose “School of Applied Knowledge” I believe.
Todaki VI - Asteroid Belt 12 being the asteroid belt you start in when you create a new character with a starting system of Todaki? I’m not sure that is even doable with just the SDE.
If it is i have no idea how you could relate your starting race/ancestry/bloodline/starting corp to a belt without making a custom table with this kind of data.