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.
 

A eve db question
Author
Previous Topic Next Topic
promixius
S.N.O.T
S.N.O.T.
#1 - 2013-05-08 10:21:52 UTC
Good day everyone

So i've been trying to fetch ships and their respective high,med,low slots for personal use for fitting options.
Now I found some info here on the forums to extract that exact information however I've seems to run into problems
it wont always find the slots it will return null instead.
Is this either just missing information or is there something wrong with the following sql statement please advice.

Quote:


Lets say $shipID is 16240 (Catalyst) it will return null in each slot

SELECT it. * , low.valueInt AS low, med.valueInt AS med, high.valueInt AS high
FROM invTypes AS it
LEFT JOIN dgmTypeAttributes AS low ON it.typeID = low.typeID AND low.attributeID = 12
LEFT JOIN dgmTypeAttributes AS med ON it.typeID = med.typeID AND med.attributeID = 13
LEFT JOIN dgmTypeAttributes AS high ON it.typeID = high.typeID AND high.attributeID = 14
WHERE it.`typeID` = $shipID
Tripsi Chord
Deep Core Mining Inc.
Caldari State
#2 - 2013-05-08 11:06:20 UTC  |  Edited by: Tripsi Chord
try this:

Quote:
SELECT it. * , coalesce(low.valueInt, low.valueFloat) AS lowslot, coalesce(med.valueInt, med.valueFloat) AS medslot, coalesce(high.valueInt, high.valueFloat) AS highslot
FROM invTypes AS it
LEFT JOIN dgmTypeAttributes AS low ON it.typeID = low.typeID AND low.attributeID = 12
LEFT JOIN dgmTypeAttributes AS med ON it.typeID = med.typeID AND med.attributeID = 13
LEFT JOIN dgmTypeAttributes AS high ON it.typeID = high.typeID AND high.attributeID = 14
WHERE it.`typeID` = 16240


You must use the column valueInt AND valutFloat on the dgmtypeattributes Table with the coalesce state.

For me this query return:

3 low, 2 med and 8 high.
promixius
S.N.O.T
S.N.O.T.
#3 - 2013-05-08 17:09:41 UTC
Ahh thank you very much for your help very much appreciated