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.
 

Need help with MySQL Query
Author
Previous Topic Next Topic
infolex
The Scope
Gallente Federation
#1 - 2012-03-28 12:51:36 UTC
I need a list of the materials for HULK

I use query (for RAW materials)
Quote:
SELECT it.typeID, it.typeName, itm.quantity
FROM invTypeMaterials itm
INNER JOIN invTypes it ON it.typeID = itm.materialTypeID
WHERE itm.typeID =22544
ORDER BY it.typeName


for Extra materials
Quote:
SELECT t.typeID, t.typeName, r.quantity
FROM ramTypeRequirements AS r
INNER JOIN invTypes AS t ON r.requiredTypeID = t.typeID
INNER JOIN invGroups AS g ON t.groupID = g.groupID
WHERE r.typeID =22545
AND r.activityID =1
AND g.categoryID !=16


First result:
typeID____typeName_____________________quantity
3828_____Construction Blocks______________50
11545____Crystalline Carbonide Armor Plate___2000
11547____Fusion Reactor Unit______________30
11531____Ion Thruster____________________40
37_______Isogen________________________17842 <---Wrong!
11535____Magnetometric Sensor Cluster______150
40_______Megacyte______________________270 <---Wrong!
36_______Mexallon______________________61280 <---Wrong!
11399____Morphite______________________72
38_______Nocxium______________________3972 <---Wrong!
11553____Oscillator Capacitor Unit__________400
11541____Photon Microprocessor___________2000
11556____Pulse Shield Emitter_____________150
35_______Pyerite_______________________645030 <---Wrong!
34_______Tritanium_____________________2120040 <---Wrong!
39_______Zydrine_______________________1030 <---Wrong!

Second result:
typeID__typeName____________ quantity
11478__R.A.M.- Starship Tech_____10
17476__Covetor________________1


I need correct SQL Query. (without WRONG positions)?

p.s. google translate
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#2 - 2012-03-28 14:02:37 UTC
http://www.fuzzwork.co.uk/blueprints/22544/-4/5/-4/5 if you just want the materials.



Anyway, the cause of the difference is that the Covetor is marked as recyclable in the ramTypeRequirements table. As such you subtract the materials in it from the materials in the invTypeMaterials table.


select typeid,name,greatest(0,sum(quantity)) quantity from (select invTypes.typeid typeid,invTypes.typeName name,quantity from invTypes,invTypeMaterials where invTypeMaterials.materialTypeID=invTypes.typeID and invTypeMaterials.TypeID=? union select invTypes.typeid typeid,invTypes.typeName name,invTypeMaterials.quantity*r.quantity*-1 quantity from invTypes,invTypeMaterials,ramTypeRequirements r,invBlueprintTypes bt where invTypeMaterials.materialTypeID=invTypes.typeID and invTypeMaterials.TypeID =r.requiredTypeID and r.typeID = bt.blueprintTypeID AND r.activityID = 1 and bt.productTypeID=? and r.recycle=1) t group by typeid,name

Where the ? is replaced with the itemid.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
infolex
The Scope
Gallente Federation
#3 - 2012-03-29 04:44:06 UTC
YES!!

+ having quantity!=0
Sidrat Flush
School of Applied Knowledge
#4 - 2012-03-29 07:29:44 UTC
Quote:
IF OBJECT_ID('[dbo].[typeBuildReqs]') IS NOT NULL
DROP TABLE [dbo].[typeBuildReqs]

CREATE TABLE [dbo].[typeBuildReqs]
(
typeID smallint,
activityID tinyint,
requiredTypeID smallint,
quantity int,
damagePerJob float,
wasted tinyint,

CONSTRAINT typetypeBuildReqs_PK PRIMARY KEY CLUSTERED
(typeID, activityID, requiredTypeID, wasted)
)

GO

INSERT INTO typeBuildReqs (typeID, activityID, requiredTypeID, quantity,
damagePerJob,wasted)

(SELECT itm1.typeID, 1 AS activityID, itm1.requiredTypeID, (itm1.quantity-
isnull(itm2.quantity,0)) as quantity, 1 AS damagePerJob, 1 AS wasted

FROM
(SELECT invBlueprintTypes.blueprintTypeID as typeID, 1 AS
activityID, invTypeMaterials.materialTypeID AS requiredTypeID,
invTypeMaterials.quantity as quantity, 1 AS damagePerJob, 1 AS wasted
FROM invTypeMaterials

INNER JOIN invBlueprintTypes
ON invTypeMaterials.typeID =
invBlueprintTypes.productTypeID) as itm1

LEFT OUTER JOIN

(SELECT t.typeID, 1 AS activityID,
itm.materialTypeID as requiredTypeID ,
(itm.quantity * t.quantity) AS quantity,
1 AS damagePerJob, 1 AS wasted
FROM
(SELECT DISTINCT rtr.typeID, rtr.requiredTypeID, rtr.quantity
FROM ramTypeRequirements AS rtr

INNER JOIN invTypes AS iT
ON rtr.requiredTypeID = iT.typeID

INNER JOIN invGroups AS iG
ON iT.groupID = iG.groupID
WHERE ((rtr.activityID = 1) AND
(rtr.recycle = 1) AND
(iG.categoryID <> 4) AND
(iG.categoryID <> 17))) AS t

INNER JOIN invTypeMaterials AS itm
ON t.requiredTypeID = itm.typeID) as itm2

on itm2.typeID = itm1.typeID and
itm2.activityID = itm1.activityID and
itm2.requiredTypeID = itm1.requiredTypeID

WHERE (itm1.quantity-isnull(itm2.quantity,0)) > 0)

UNION

(SELECT rtr2.typeID, rtr2.activityID, rtr2.requiredTypeID, rtr2.quantity,
rtr2.damagePerJob, 0 AS wasted
FROM ramTypeRequirements AS rtr2

INNER JOIN invTypes AS types
ON rtr2.requiredTypeID = types.typeID

INNER JOIN invBlueprintTypes AS bps
ON rtr2.typeID = bps.blueprintTypeID

INNER JOIN invGroups AS groups
ON types.groupID = groups.groupID

LEFT OUTER JOIN (SELECT typeID, materialTypeID, quantity
FROM invTypeMaterials) AS itm
ON (bps.productTypeID = itm.typeID AND
rtr2.requiredTypeID = itm.materialTypeID AND
(rtr2.quantity <= itm.quantity OR
rtr2.quantity > itm.quantity OR itm.quantity is null))

WHERE ((groups.categoryID <> 16) AND (rtr2.activityID = 1) AND
(rtr2.quantity > 0)))
GO


This was done by someone else but I'm sorry to say can't remember their name. MS SQL

Its time to stand up against the bad empire based CEO telling falsehoods about what new characters can accomplish and pushing them towards an in game experience of drudgery and loneliness keeping them in the shadow of ignorance for at nest their own profit at worse apathy towards all the experiences that Eve has to offer.