Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Lexie Thiesant
The Scope Gallente Federation
0
|
Posted - 2013.06.09 16:07:00 -
[1] - Quote
I am new to mysql, and this took me awhile to figure out. I still not sure on the Joins and stuff. I would have liked to added the eveIcon.iconFile to the end and merged the AttributeName and SkillLvl fields.
SET @varTYPEID := '3425'; SELECT inv.typeName, `t1 dgmAttributeTypes`.displayName AS AttributeType, CASE WHEN dgmTypeAttributes.valueInt < 18 THEN concat( valueInt, eveUnits.displayName ) WHEN dgmTypeAttributes.valueInt < 1000 THEN dgmAttributeTypes.displayName WHEN dgmTypeAttributes.valueInt IS NULL THEN concat( valueFloat, eveUnits.displayName ) ELSE invTypes.typeName END AS AttributeName, CASE WHEN `t1 dgmAttributeTypes`.attributeID = 182 THEN (SELECT valueInt FROM dgmTypeAttributes WHERE attributeID = 277 AND typeID = @varTYPEID) WHEN `t1 dgmAttributeTypes`.attributeID = 183 THEN (SELECT valueInt FROM dgmTypeAttributes WHERE attributeID = 278 AND typeID = @varTYPEID) WHEN `t1 dgmAttributeTypes`.attributeID = 184 THEN (SELECT valueInt FROM dgmTypeAttributes WHERE attributeID = 279 AND typeID = @varTYPEID) ELSE NULL END AS SkillLvl FROM invTypes inv INNER JOIN dgmTypeAttributes ON inv.typeID = dgmTypeAttributes.typeID LEFT OUTER JOIN invTypes ON dgmTypeAttributes.valueInt = invTypes.typeID INNER JOIN dgmAttributeTypes `t1 dgmAttributeTypes` ON dgmTypeAttributes.attributeID = `t1 dgmAttributeTypes`.attributeID LEFT OUTER JOIN eveUnits ON `t1 dgmAttributeTypes`.unitID = eveUnits.unitID LEFT OUTER JOIN dgmAttributeTypes ON dgmTypeAttributes.valueInt = dgmAttributeTypes.attributeID WHERE inv.typeID = @varTYPEID AND `t1 dgmAttributeTypes`.displayName IS NOT NULL |
Steve Ronuken
Fuzzwork Enterprises
1503
|
Posted - 2013.06.09 19:02:00 -
[2] - Quote
http://wiki.eve-id.net/SQL_Methods_on_the_dump#Skills may prove of interest Steve Ronuken for CSM 9!-á I'm starting early :) Handy tools and an SDE conversion Twitter: @fuzzysteve on Twitter |
Lexie Thiesant
The Scope Gallente Federation
0
|
Posted - 2013.06.12 19:49:00 -
[3] - Quote
hmmm it dosent show my skill level required on all typeIDs not sure where its failing
example s
typeID = 22578
+-----------------+---------------------------+---------------+----------+ | typeName | AttributeType | AttributeName | SkillLvl | +-----------------+---------------------------+---------------+----------+ | Mining Upgrades | Primary attribute | Memory | NULL | | Mining Upgrades | Secondary attribute | Intelligence | NULL | | Mining Upgrades | Primary Skill required | Mining | NULL | | Mining Upgrades | Training time multiplier | 4x | NULL | | Mining Upgrades | Level | NULL | NULL | +-----------------+---------------------------+---------------+----------+
this one works though
typeID = 3425
+-----------------+---------------------------+---------------+----------+ | typeName | AttributeType | AttributeName | SkillLvl | +-----------------+---------------------------+---------------+----------+ | Shield Upgrades | Primary attribute | Intelligence | NULL | | Shield Upgrades | Secondary attribute | Memory | NULL | | Shield Upgrades | Primary Skill required | Engineering | 2 | | Shield Upgrades | Secondary Skill required | Science | 1 | | Shield Upgrades | Training time multiplier | 2x | NULL | | Shield Upgrades | Level | NULL | NULL | | Shield Upgrades | Power Need Bonus | -5% | NULL | +-----------------+---------------------------+---------------+----------+ |
Rob Crowley
State War Academy
68
|
Posted - 2013.06.12 20:24:00 -
[4] - Quote
Lexie Thiesant wrote:hmmm it dosent show my skill level required on all typeIDs not sure where its failing That's because sometimes valueFloat is used instead of valueInt. It seems to be pretty random, so you should pretty much always check both fields. |
Lexie Thiesant
The Scope Gallente Federation
0
|
Posted - 2013.06.15 13:37:00 -
[5] - Quote
Thanks fixed the case statement
CASE WHEN `t1 dgmAttributeTypes`.attributeID = 182 THEN (SELECT IFNULL(valueInt, valueFloat) FROM dgmTypeAttributes WHERE attributeID = 277 AND typeID = @varTYPEID) WHEN `t1 dgmAttributeTypes`.attributeID = 183 THEN (SELECT IFNULL(valueInt, valueFloat) FROM dgmTypeAttributes WHERE attributeID = 278 AND typeID = @varTYPEID) WHEN `t1 dgmAttributeTypes`.attributeID = 184 THEN (SELECT IFNULL(valueInt, valueFloat) FROM dgmTypeAttributes WHERE attributeID = 279 AND typeID = @varTYPEID) ELSE NULL END AS SkillLvl |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |