Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Kazuo Ishiguro
House of Marbles Zzz
|
Posted - 2009.05.07 08:34:00 -
[1]
When I try to run this query:
select typeName, volume, portionSize, typeID from invTypesinner join invGroups on invTypes.groupID = invGroups.groupID inner join invBlueprintTypes on invBlueprintTypes.productTypeID = invTypes.typeID inner join invMarketGroups on invMarketGroups.marketGroupName = invGroups.groupName where invTypes.published = 'true' AND invTypes.marketGroupID > 0 AND invBlueprintTypes.techLevel = 1 /* T1 items only */ AND invMarketGroups.parentGroupID <> 955 /* No rigs */ AND invMarketGroups.parentGroupID <> 943 /* No rigs */ AND (invGroups.categoryID = 7 OR invGroups.categoryID = 8) /* Modules and charges only */
on a fresh restore of the latest dump, using SQL Server 2005, I get this error:
Quote: Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Latin1_General_BIN" in the equal to operation.
My aim is for it to return one row for each manufacturable T1 charge or module, and no other items. Any suggestions? Changing the collation of the database seems to make no difference. --- 20:1 mineral compression ISRC Racing, Season 7 - schedule |
DeTox MinRohim
Madhatters Inc. The Initiative.
|
Posted - 2009.05.07 10:30:00 -
[2]
Edited by: DeTox MinRohim on 07/05/2009 10:34:27 change this part:
on invMarketGroups.marketGroupName COLLATE Latin1_General_CI_AI = invGroups.groupName
Your final query
SELECT typeID, typeName, capacity, portionSize FROM invTypes INNER JOIN invGroups ON invTypes.groupID = invGroups.groupID INNER JOIN invBlueprintTypes ON invBlueprintTypes.productTypeID = invTypes.typeID INNER JOIN invMarketGroups ON invMarketGroups.marketGroupName COLLATE Latin1_General_CI_AI = invGroups.groupName WHERE invTypes.published = '1' AND invTypes.marketGroupID > '0' AND invBlueprintTypes.techLevel = '1' AND invMarketGroups.parentGroupID <> '955' AND invMarketGroups.parentGroupID <> '943' AND (invGroups.categoryID = '7' OR invGroups.categoryID = '8');
------ This sig space is Read-only ! omgalink - Online Skillsheet |
Melonar
Core Element Blackguard Coalition
|
Posted - 2009.05.07 15:11:00 -
[3]
You would probably do better to join on the ID numbers than the names. The IDs are indexed (by virtue of being the primary keys). The IDs are also integers which eliminates the collation issue entirely.
A few other points:
This line: "AND invTypes.marketGroupID > 0" is not needed as ID 0 is not in the invMarketGroups table and would be eliminated by the join.
The checks on invMarketGroups.parentGroupID can be simplified to a single line: "AND invMarketGroups.parentGroupID not in (955, 943)"
The checks on invGroups.categoryID can be simplified to: "AND invGroups.categoryID in (7, 8)"
So, in summary, here are my recommended changes to your query:
select typeName, volume, portionSize, typeID from invTypesinner join invGroups on invTypes.groupID = invGroups.groupID inner join invBlueprintTypes on invBlueprintTypes.productTypeID = invTypes.typeID inner join invMarketGroups on invMarketGroups.marketGroupID = invGroups.groupID where invTypes.published = 'true' AND invBlueprintTypes.techLevel = 1 /* T1 items only */ AND invMarketGroups.parentGroupID NOT IN (943, 955) /* No rigs */ AND invGroups.categoryID IN (7, 8) /* Modules and charges only */
|
Dav Varan
|
Posted - 2009.05.07 15:44:00 -
[4]
Looks wrong to join Marketgroup and group the two are not directly related.
InvMarketGroup joins to invtypes via .marketgroupid InvGroup joins to invtypes via .groupid
iirc
|
Kazuo Ishiguro
House of Marbles Zzz
|
Posted - 2009.05.07 17:53:00 -
[5]
Edited by: Kazuo Ishiguro on 07/05/2009 17:54:05 3rd reply had it more or less right - I was trying to join invTypes via invGroups to invMarketGroups using an invalid relation, when in fact there's a valid direct link. In the end, I used
SELECT typeName, volume, portionSize, typeID from invTypesinner join invGroups on invTypes.groupID = invGroups.groupID inner join invBlueprintTypes on invBlueprintTypes.productTypeID = invTypes.typeID inner join invMarketGroups on invMarketGroups.marketGroupID = invTypes.marketGroupID where invTypes.published = 'true' /* No silly GM or Dev stuff */ AND invTypes.marketGroupID > 0 /* Excludes most faction items */ AND invBlueprintTypes.techLevel = 1 /* T1 items only */ AND invMarketGroups.parentGroupID <> 955 /* No rigs */ AND invMarketGroups.parentGroupID <> 943 /* No rigs */ AND invMarketGroups.parentGroupID <> 1111 /* No rigs */ AND (invGroups.categoryID = 7 OR invGroups.categoryID = 8) /* Modules and charges only */ order by typeName
That gave me what I needed, as I already had a list of build requirements for each item. Any suggestions for a query that would return, in an extra column, the volume of minerals for these items when refined? This seems to be quite difficult to do...
Also, thanks for the other suggestions. --- 20:1 mineral compression ISRC Racing, Season 7 - schedule |
Melonar
Core Element Blackguard Coalition
|
Posted - 2009.05.07 18:27:00 -
[6]
For the mineral volume, it is the total sum of minerals * 0.01 m3. All of the minerals have the exact same volume (The ore does not, but minerals do)
The trick is to get the mineral counts for refine (especially for named items).
|
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |