These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Tech III Items SQLite Query
Author
Previous Topic Next Topic
Cindril
Hulk Industries
#1 - 2012-03-05 22:47:55 UTC
Hey,

I'm trying to get a list of all Tech III items in the database using the following query in SQLite:

Quote:
SELECT t.typeID,t.typeName
FROM invTypes as t, invMetaGroups as m, invMetaTypes as mt
WHERE t.typeID=mt.typeID and m.metaGroupID=mt.metaGroupID
and m.metaGroupID=14 ORDER BY t.typeName;


This works as expected and gives me 60 rows of Tech III items. However, comparing the list to the actual market there are some items missing.

Specifically, in the case of "Legion Defense" items, "Adaptive Augmenter" is not showing up in the query. There seems to be one item missing from every "category" (if you want to call it like that, "Legion Electronics", and so on).

I'm using cru110-sqlite3-v1.db by the way. Any ideas?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#2 - 2012-03-05 23:11:20 UTC  |  Edited by: Steve Ronuken
Cheap and nasty Blink

SELECT typeID, typeName
FROM invTypes
WHERE marketgroupid
IN ( 1126, 1118, 1122, 1130, 1134, 1128, 1121, 1125, 1133, 1137, 1129, 1120, 1124, 1132, 1136, 1127, 1119, 1123, 1131, 1135 )


Slightly less nasty:


SELECT typeID, typeName
FROM invTypes,invmarketgroups
WHERE invTypes.marketgroupid=invMarketGroups.marketgroupid and
parentgroupid IN ( 1113, 1114, 1115, 1116 )


Least nasty (depending on your definition of nasty)

SELECT typeID, typeName
FROM invTypes, invmarketgroups img1, invmarketgroups img2
WHERE invTypes.marketgroupid = img1.marketgroupid
AND img1.parentgroupid = img2.marketgroupid
AND img2.parentgroupid =1112

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Cindril
Hulk Industries
#3 - 2012-03-05 23:35:47 UTC
Great, thank you!
Indalecia
#4 - 2012-05-17 15:19:43 UTC  |  Edited by: Indalecia
Bump.

This is still not fixed in the Escalation database dump. There are workarounds, but it makes it difficult to build reliable, consistent applications that use the database dump.

Please fix it!

Here's a summary of the problem:
http://paste.artefact2.com/p/225c479

This happens for all four T3 cruisers, and for all subsystem types (always one missing metagroupid out of the four variants).

This also happens for a LOT of miscellaneous items (fittable modules that *should* have a metagroupid), the full list is here:
http://paste.artefact2.com/p/43e21fe

I somewhat fixed it by using the techlevel and metagroup attribute values, with a view like this:
http://paste.artefact2.com/p/bf5a9f0 (only fittable modules)

https://o.smium.org/ — v0.13.5 — A browser-based fitting tool and loadout sharing platform