Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.02 02:54:00 -
[1] - Quote
Hi Everyone,
I have been plugging away at trying to pull the blueprints from the market .. but I have seem to run into a snag ..
CCP FoxFour was kind enough to give me this website .. http://wiki.eve-id.net/Published_Blueprints which works , but its pulling every blue print in the game ..
What I am needing help with is how do I filter or run another SQL statement to just pull the BPO's that are on the market ..
hope that makes sense .. and any help would be great ..
thanks a lot .. and fly safe .. |
ItsmeHcK1
Kicked. Shadow Cartel
113
|
Posted - 2014.06.02 06:20:00 -
[2] - Quote
You could crossreference the market groups... if a blueprint doesn't have a market group, it's not on the market. |
Rob Crowley
State War Academy
300
|
Posted - 2014.06.02 06:22:00 -
[3] - Quote
You can't do that just with the SDE cause it doesn't contain information if and how a certain BP type is seeded. You'd have to use market data of some form (either CREST or one of the player-made DBs) to filter out the market-seeded BPOs. |
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.02 19:09:00 -
[4] - Quote
Thanks for the advice guys ..
Right now it out puts every blue print in the game which is some crazy number of almost 3000 ... So once i have that i open excel and then start deleting stuff .. but once i got the tech 2, out and stuff like that .. i noticed i still had tech 2 and other blueprints i have never heard or seen before ..
So wasnt sure how to get a list of market blue prints .. if that makes sense .. |
Mintoko
Taedium In Perpetuam
22
|
Posted - 2014.06.05 02:02:00 -
[5] - Quote
A check for blueprints that are published and have a marketGroupID yields a list of 1,402.
SELECT * FROM invTypes WHERE typeName LIKE '%Blueprint' AND published = 1 AND marketGroupID > 'null';
This doesn't solve the problem that Rob mentioned, but it does cut the list in half.
|
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
3380
|
Posted - 2014.06.05 11:56:00 -
[6] - Quote
Try eliminating everything with a base price of 9999999 or 0
That /should/ cut out all the T2 blueprints, and prints that are only available as copies. Woo! CSM 9! http://fuzzwork.enterprises/ Twitter: @fuzzysteve on Twitter |
Rob Crowley
State War Academy
300
|
Posted - 2014.06.05 13:59:00 -
[7] - Quote
Steve Ronuken wrote:Try eliminating everything with a base price of 9999999 or 0
That /should/ cut out all the T2 blueprints, and prints that are only available as copies. There are still plenty of T2 ships and a couple modules like ASBs left. The ships might possibly have existing T2 BPOs though. |
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.06 15:56:00 -
[8] - Quote
Mintoko wrote:A check for blueprints that are published and have a marketGroupID yields a list of 1,402.
SELECT * FROM invTypes WHERE typeName LIKE '%Blueprint' AND published = 1 AND marketGroupID > 'null';
This doesn't solve the problem that Rob mentioned, but it does cut the list in half.
Thanks for the information will try it out tonight and see what happens .. thanks again everyone for all the help .. I think with all this information i can finally make a bpo checklist ..
thanks ..
|
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.07 15:59:00 -
[9] - Quote
Mintoko wrote:A check for blueprints that are published and have a marketGroupID yields a list of 1,402.
SELECT * FROM invTypes WHERE typeName LIKE '%Blueprint' AND published = 1 AND marketGroupID > 'null';
This doesn't solve the problem that Rob mentioned, but it does cut the list in half.
Hey Mintoko,
I tried to run that statement you said , but keeps returning a error ... I even added to the statement I ran before and same error ..
Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'null' to data type int.
any thoughts ??
thanks .. |
Althalus Stenory
Flying Blacksmiths
18
|
Posted - 2014.06.07 16:36:00 -
[10] - Quote
Change the query to this, if you use it :
SELECT * FROM invTypes WHERE typeName LIKE '%Blueprint' AND published = 1 AND marketGroupID IS NOT NULL; |
|
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.08 15:09:00 -
[11] - Quote
Althalus Stenory wrote:Change the query to this, if you use it :
SELECT * FROM invTypes WHERE typeName LIKE '%Blueprint' AND published = 1 AND marketGroupID IS NOT NULL;
Thanks Athalus that worked out great .... Now I just need to figure out how to translate the groupid and the marketgroupid into the names and I am also have all the parts needed to make a bpo checklist ..
thanks everyone for the help .. |
Desmont McCallock
384
|
Posted - 2014.06.08 15:19:00 -
[12] - Quote
Maybe this query serves you better.
SELECT typeName, groupName, marketGroupName FROM invTypes JOIN invGroups ON invGroups.groupID = invTypes.groupID JOIN invMarketGroups ON invMarketGroups.marketGroupID = invTypes.marketGroupID JOIN invBlueprintTypes ON invBlueprintTypes.blueprintTypeID = invTypes.typeID WHERE invTypes.published = 1 AND invTypes.marketGroupID IS NOT NULL |
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.08 15:22:00 -
[13] - Quote
Desmont McCallock wrote:Maybe this query serves you better.
SELECT typeName, groupName, marketGroupName FROM invTypes JOIN invGroups ON invGroups.groupID = invTypes.groupID JOIN invMarketGroups ON invMarketGroups.marketGroupID = invTypes.marketGroupID JOIN invBlueprintTypes ON invBlueprintTypes.blueprintTypeID = invTypes.typeID WHERE invTypes.published = 1 AND invTypes.marketGroupID IS NOT NULL
WOW thanks Desmont that is perfect .. looks like it pulled all the market bpo's and has the names and everything ... but the only thing it's missing is the "typeID" field .. that is the field I have been using to do the compare in excel ...
thanks a lot !!!
now its excel time ... LOL |
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.08 15:26:00 -
[14] - Quote
Thinking I am getting better at SQL .. I just added the "typedid" to the front of the line and it worked ...
Sweet |
Rob Crowley
State War Academy
301
|
Posted - 2014.06.08 15:26:00 -
[15] - Quote
SELECT it.typeID,it.typeName,it.basePrice,ig.groupName,img.marketGroupName FROM invTypes it JOIN invMarketGroups img ON it.marketGroupID=img.marketGroupID JOIN invGroups ig ON it.groupID=ig.groupID WHERE ig.categoryID=9 AND it.published=1 AND it.marketGroupID IS NOT NULL AND it.basePrice<>0 |
Desmont McCallock
384
|
Posted - 2014.06.08 15:34:00 -
[16] - Quote
Further more if you aren't interested in the T2 blueprints add "AND basePrice <> 0" at the end of my query as Rob Crowley also provides.
And yes, if you want more columns to show up just add the column name between the SELECT and the FROM statement. |
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.08 18:20:00 -
[17] - Quote
Desmont McCallock wrote:Further more if you aren't interested in the T2 blueprints add "AND basePrice <> 0" at the end of my query as Rob Crowley also provides.
And yes, if you want more columns to show up just add the column name between the SELECT and the FROM statement.
Thanks Desmont for all the help .. that worked out just like I needed it too ..
Now just need to fig out if there is a way to import my assest into the sql server ... |
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.08 19:18:00 -
[18] - Quote
Desmont McCallock wrote:Further more if you aren't interested in the T2 blueprints add "AND basePrice <> 0" at the end of my query as Rob Crowley also provides.
And yes, if you want more columns to show up just add the column name between the SELECT and the FROM statement.
Hey Desmont I did what you said and added that code to the end of the line , but still coming up with T2 blueprints .. I see the Crow and Claw in there ...
So close .... |
Rob Crowley
State War Academy
301
|
Posted - 2014.06.08 19:45:00 -
[19] - Quote
Aloron wrote:Hey Desmont I did what you said and added that code to the end of the line , but still coming up with T2 blueprints .. I see the Crow and Claw in there ...
So close .... Yeah, as I said before this list will not be perfect. As I claimed in my first post I still think you can't get a perfect list just with SDE data without mixing in some market data, maybe someone will prove me wrong, but for now that query is the closest we can get you. |
Aloron
Brutor Tribe Minmatar Republic
4
|
Posted - 2014.06.08 21:35:00 -
[20] - Quote
Rob Crowley wrote:Aloron wrote:Hey Desmont I did what you said and added that code to the end of the line , but still coming up with T2 blueprints .. I see the Crow and Claw in there ...
So close .... Yeah, as I said before this list will not be perfect. As I claimed in my first post I still think you can't get a perfect list just with SDE data without mixing in some market data, maybe someone will prove me wrong, but for now that query is the closest we can get you. Edit: Actually, getting rid of the T2s is pretty easy: SELECT it.typeID,it.typeName,ig.groupName,img.marketGroupName FROM invTypes it JOIN invMarketGroups img ON it.marketGroupID=img.marketGroupID JOIN invGroups ig ON it.groupID=ig.groupID JOIN invBlueprintTypes ibt ON it.typeID=ibt.blueprintTypeID WHERE ig.categoryID=9 AND it.published=1 AND it.marketGroupID IS NOT NULL AND it.basePrice<>0 AND ibt.techLevel=1 The really problematic ones are the exploration seeded ones like ASBs.
Thanks Rob, that seem to work very nice seems to works ... what is funny is you would think some builder with in the last 10 years would have figure this out , if they wanted to own all the blueprints on the market ..
now just wish I could figure out the API stuff so I could import my blueprints into sql so that way I could good connect excel to it and make a list to compare .. |
|
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |