|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-06 : 20:27:54
|
| Jesse writes "Alright... this is similar to your unique sorting using an additional table, though I am not certain that that would work out for me. I have a table that lists several pieces of information about an item. The three columns I am worried about are Material, Quality and Item. When the current query is run, it lists them by material, then quality, then item. This is only done properly at the moment because i have netered the info into the tables in this order.Now, these columns are called with the following query:SELECT Quality, Category, Item, ValueGold, ValueSilver, ValueCopper FROM Items, Skills, Realms WHERE Items.RealmNum = Realms.RealmNum && Realms.RealmName = '$realmselect' && Items.SkillNum = Skills.SkillNum && Skills.SkillName = '$craftselect'The table designs are as follows:CREATE TABLE `Items` ( `ItemNum` int(8) NOT NULL auto_increment, `RealmNum` tinyint(1) NOT NULL default '0', `SkillNum` smallint(3) NOT NULL default '0', `Category` varchar(45) NOT NULL default '', `Quality` varchar(45) NOT NULL default '', `Item` varchar(45) NOT NULL default '', `ValueGold` smallint(3) default NULL, `ValueSilver` smallint(3) default NULL, `ValueCopper` smallint(3) default NULL, `PictureURL` varchar(75) default NULL, `Description` longtext, `Misc` tinyint(1) default NULL, PRIMARY KEY (`ItemNum`), UNIQUE KEY `ItemNum` (`ItemNum`))CREATE TABLE `Realms` ( `RealmNum` tinyint(1) NOT NULL auto_increment, `RealmName` varchar(10) NOT NULL default '', PRIMARY KEY (`RealmNum`), UNIQUE KEY `RealmNum` (`RealmNum`,`RealmName`))CREATE TABLE `Skills` ( `SkillNum` smallint(3) NOT NULL auto_increment, `SkillName` varchar(25) NOT NULL default '', PRIMARY KEY (`SkillNum`), UNIQUE KEY `SkillNum` (`SkillNum`,`SkillName`))Those are submitted so you can see current associations. Categroy, Quality and Item need to be able to be sorted by a specified order (iron, steel, mithril, adamantium), but also, those orders may change based on RealmNum and SkillNum values. I was trying something like this (yeah, I know, but it was worth a try):SELECT Quality, Category, Item, ValueGold, ValueSilver, ValueCopper FROM Items, Skills, Realms WHERE Items.RealmNum = Realms.RealmNum && Realms.RealmName = 'Midgard' && Items.SkillNum = Skills.SkillNum && Skills.SkillName = 'Tailoring' SORT BY Items.Category('Padded', 'Rawhide', 'Tanned', 'Cured', 'Hard', 'Rigid', 'Embossed', 'Imbued'), Items.Quality('Mjuklaedar', 'Svarlaeder', 'Starklaedar', 'Woolen', 'Linen', 'Brokade', 'Silk', 'Gossamer', 'Sylvan', 'Seamist'), Items.Item('Gloves', 'Helm', 'Boots', 'Leggings', 'Sleeves', 'Jerkin', 'Full Suit', 'FullSuit')Any suggestions?" |
|
Custom SORT BY... multiple values, multiple columns