| Pages: [1] :: one page |
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Neroo Tal
Minmatar
Order of the Golden Dawn
Shades of Gray
   |
Posted - 2010.12.27 21:26:00 -
[1]
Not sure where to put this honestly, so hope this is the correct forum. I've been working with the data dump for a few days now, trying to muck my way through it, found a few pre-made mysql queries that had some of the information I wanted but not everything, so I played around with them a bit until I started seeing the connections that the tables began to form.
I will show you what I have, what I'm looking for is if there is a more efficient way to do the query itself and how to garner a bit more information from the queries and where to attain that information
Most specifically is this monstrosity that I've created that pulls all the relevant data I want / need for any items in game that are published
SELECT t.typeName, t.typeID, t.groupID, t.description, d.attributeName, d.displayName, d.description, a.valueInt, a.valueFloat, i.iconFile
FROM invTypes AS t
INNER JOIN dgmtypeattributes AS a
INNER JOIN dgmattributetypes AS d
INNER JOIN eveicons AS i
WHERE t.typeID = 1202 --Civilian Mining Drone
AND t.published = 1
AND t.typeID = a.typeID
AND a.attributeID = d.attributeID
AND d.iconID = i.iconID
This pulls up all the relevant data of any item by typeID; there has to be a more efficient clause than this that will extract all the necessary data and still confine it to the same / similar restraints.
From this info how would I find the icon associated with the item itself ( in this case Civilian Mining Drone ) that would show up if I looked at the item in market ( ie: search for civilian and click on civilian mining drone, the icon to the left of the words 'Civilian Mining Drone' ).
--Neroo Tal
Order of the Golden Dawn CEO : Founder |
Tonto Auri
Vhero' Multipurpose Corp
|
Posted - 2010.12.28 00:09:00 -
[2]
0. Wrap your query, don't break forum layout.
1. LEFT OUTER JOIN
2. What you mean "From this info how would I find the icon associated with the item itself" if you're requesting it directly in the query?
--
Thanks CCP for cu |
Catari Taga
Centre Of Attention
Middle of Nowhere
|
Posted - 2010.12.28 00:10:00 -
[3]
Edited by: Catari Taga on 28/12/2010 00:17:02
First of all, your query does not pull in all relevant data due to your inner joins, try
SELECT dat.attributeName, dat.displayName, IFNULL(dta.valueInt,dta.valueFloat) AS value,
u.displayName as unit
FROM invTypes i
JOIN dgmtypeattributes dta ON i.typeID = dta.typeID
LEFT JOIN dgmattributetypes dat ON dta.attributeID = dat.attributeID
LEFT JOIN eveunits u ON u.unitID = dat.unitID
WHERE i.typeID=1202
(that's the standard query I use, adjust the select to your liking to add your additional columns in again if you want)
As to the images you are looking for, they are stored in the imagedump via their typeID, i.e. in this case in /types/dronetypes_png/32_32/1202.png. edit to add: Only equipment and similar icons/ need to be queried from the eveIcons table.
--
|
Neroo Tal
Minmatar
Order of the Golden Dawn
Shades of Gray
|
Posted - 2010.12.29 20:10:00 -
[4]
Edited by: Neroo Tal on 29/12/2010 20:11:07
@Catari Taga: Thanks, I don't understand everything in your query but it seems to be more efficient than mine, which is what I wanted. It also pulls up missing data that I wasn't even aware was being missed.
Now question about marketGroupID, I did find out that the parentGroupID is just a marketGroupID under a different name and a recursive search will inevitably yield the market chain this item falls under. Is there a way to recursively go look up the marketGroupID chain in SQL that will produce a chain of market labels? i.e.:
Medium Shield Booster II (typeID 10850)
Medium -> Shield Boosters -> Shield -> Ship Equipment
--Edit Spelling
--Neroo Tal
Order of the Golden Dawn CEO : Founder |
uNople
Caldari
|
Posted - 2010.12.30 01:30:00 -
[5]
@Neroo.
If you're confused about joins, here's a good place to start.
Basically, you were doing a non-explicit inner join. You specified the columns between the tables in the where clause which is why it works the same as a proper join. A proper join is quicker because (as I understand it), when the SQL server compiles and runs your code it makes the query slower this way than when you tell it how to join.
Here's a (hopefully simple) example.
A snippet of Catari Taga's one, without select or where clauses
 Quote:
FROM invTypes i
JOIN dgmtypeattributes dta ON i.typeID = dta.typeID
LEFT JOIN dgmattributetypes dat ON dta.attributeID = dat.attributeID
LEFT JOIN eveunits u ON u.unitID = dat.unitID
now the same, rewritten without the joins.
Quote:
FROM invTypes i
JOIN dgmtypeattributes dta
LEFT JOIN dgmattributetypes dat
LEFT JOIN eveunits u
WHERE 1=1
AND i.typeID = dta.typeID
AND dta.attributeID = dat.attributeID
AND u.unitID = dat.unitID
So the difference between the two is you've moved the joining clauses* to the where clause, instead of having it on the same line. Considering you already had the joins on the right line, you just needed to add the clause to the join.
*joining clauses tell SQL which columns two tables can be joined by
As for your other question, you can join more than once to the same table (although the more joins, the worse the query performs).
I'm not sure how many levels you could have, and generally loops and such should be done at a code level rather than in SQL, but you can do something similar to this if you know how many levels deep you need:
select i.MarketGroupId,i1.MarketGroupId,i2.MarketGroupId --etc
from invTypes i
join invTypes i1 on i.parentGroupID = i1.MarketGroupId
join invTypes i2 on i1.parentGroupID = i2.MarketGroupId
where i.typeID=1202
I hope this helps you on your way.
|
Tonto Auri
Vhero' Multipurpose Corp
|
Posted - 2010.12.31 17:45:00 -
[6]
If your database have foreign keys defined, JOIN operaton will try to use them first, then primary keys, then similarly named columns, if any of these optimizations is appropriate for chosen tables.
--
Thanks CCP for cu |
Neroo Tal
Minmatar
Order of the Golden Dawn
Shades of Gray
|
Posted - 2011.01.05 03:20:00 -
[7]
Any good SQL to find the type of an item for the picture under 'types' in the image dump? (ie: shiptypes_png or stationtypes_png)
--Neroo Tal
Order of the Golden Dawn CEO : Founder |
Ruziel
Minmatar
Twilight Military Industrial Complex
|
Posted - 2011.01.05 08:34:00 -
[8]
Originally by: Neroo Tal
Any good SQL to find the type of an item for the picture under 'types' in the image dump? (ie: shiptypes_png or stationtypes_png)
Those images are all named by <typeID>.png where <typeID> is equal to invTypes.typeID.
If you don't know what kind of item it is beforehand, you will probably have to join in he invGroups and invCategories tables to sort that out.
|
Neroo Tal
Minmatar
Order of the Golden Dawn
Shades of Gray
|
Posted - 2011.01.05 19:01:00 -
[9]
Thanks, that ended up doing it for me, much appreciated.
--Neroo Tal
Order of the Golden Dawn CEO : Founder |
| |
|
| Pages: [1] :: one page |
| First page | Previous page | Next page | Last page |