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.
 

MySQL Query Assistance with Market Items
Author
Previous Topic Next Topic
Zynen
#1 - 2012-01-25 06:56:14 UTC
Hi everyone,

I need help to get a list of all the items that are available for sale on the market, from the crucible database dump.

Looking at invTypes, perhaps I need to filter by groupID (?) and published. But I have yet to find anything that tells me what groupID (or other variable) to filter by.


Any help would be awesome!
Lutz Major
Austriae Est Imperare Orbi Universo
#2 - 2012-01-25 07:35:52 UTC
AND marketGroupID IS NOT NULL :-)
Zynen
#3 - 2012-01-25 17:51:42 UTC
Your awesome, thanks Lutz!
Aineko Macx
#4 - 2012-02-08 06:36:55 UTC
Another question:

Considering the parent <-> child group relationship in invMarketGroups that forms a tree hierarchy of arbitrary depth, how can I select all leaf groups by specifying a parent at an arbitrary level? All solutions I found so far are based on expanding the table with hierarchy data or iteratively processing the results in some programming language. Using just SQL I can at best get all leaf groups with:

SELECT t1.* FROM `invMarketGroups` as t1
LEFT JOIN `invMarketGroups` as t2
ON t1.marketGroupId = t2.parentGroupId
WHERE t2.marketGroupId IS NULL

Any ideas?

iveeCore 3.0: The PHP engine for industrial activities and CREST library
Jenn Makanen
Doomheim
#5 - 2012-02-08 09:45:08 UTC
It's a shame it's not in oracle, as there's a nice connect by function you can use to select a hierarchy easily.

In mysql, you're pretty much limited to using recursion.

http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

Should give you a grounding.

Oracle express edition is nice (and free), and would do most people. just none of the regular eve tools will be set up to use it.