How do I narrow down SQL query results?

For my big SQL class project, I’m creating a database based on the character classes in World of Warcraft Classic. 8 races, 9 classes, 3 specs per class, and each spec falls into a specific role (Melee damage, spell damage, healing, tank, pet).

I created tables based on all of this and added fake players, and the database came out beautifully. My problem is running queries. If I try searching for just healers, it’ll pull up EVERY Priest, Paladin, Shaman, and Druid in the database when I only want characters with the Discipline, Holy, or Restoration specs.

This is my code for the character class table:

CREATE TABLE TOONCLASS (
ClassName   Char(10)    NOT NULL,
ClassType   Char(15)    NOT NULL, 
ArmorClass  Char(10)    NOT NULL,
Restriction Char(15)    NULL,
CONSTRAINT  ClassPK     PRIMARY KEY(ClassName),
CONSTRAINT  ClassValues CHECK (ClassName IN ('Druid', 'Hunter', 'Mage',
                'Paladin', 'Priest', 'Rogue', 'Shaman',
                'Warlock', 'Warrior')),
CONSTRAINT  ClassType   CHECK (ClassType IN ('Melee', 'Spellcaster', 'Hybrid')),
CONSTRAINT  TypeGroups  CHECK (
                (ClassName IN ('Druid', 'Shaman', 'Paladin') AND ClassType = 'Hybrid')
                OR (ClassName IN ('Mage', 'Priest', 'Warlock') AND ClassType = 'Spellcaster')
                OR (ClassName IN ('Hunter', 'Rogue', 'Warrior') AND ClassType = 'Melee')), 

CONSTRAINT  ArmorType   CHECK (ArmorClass IN ('Cloth', 'Leather', 'Mail', 'Plate')),
CONSTRAINT  ArmorGroups CHECK(
                (ClassName IN ('Mage', 'Priest', 'Warlock') AND ArmorClass = 'Cloth')
                OR (ClassName IN ('Druid', 'Rogue') AND ArmorClass = 'Leather')
                OR (ClassName IN ('Hunter', 'Shaman') AND ArmorClass = 'Mail')
                OR (ClassName IN ('Paladin', 'Warrior') AND ArmorClass = 'Plate')),
CONSTRAINT  ClassRestrict   CHECK (Restriction IN ('Alliance Only', 'Horde Only', NULL)),
CONSTRAINT  RestrictGroups  CHECK (
                (ClassName = 'Paladin' AND Restriction = 'Alliance Only')
                OR (ClassName = 'Shaman' AND Restriction = 'Horde Only')
                OR (Restriction = NULL)),
);

This is my code for the spec table:

CREATE TABLE TOONSPEC(
SpecName    Char(20)    NOT NULL,
ClassName   Char(10)    NOT NULL,
SpecRole    Char(20)    NOT NULL,
CONSTRAINT  SpecPK      PRIMARY KEY (SpecName),
CONSTRAINT  ClassFK     FOREIGN KEY (ClassName)
                REFERENCES TOONCLASS (ClassName),
CONSTRAINT  RoleGroups  CHECK (
                (SpecName IN ('Discipline', 'Holy(Pa)', 'Holy(Pr)', 'Restoration(Dr)', 
                'Restoration(Sh)') AND SpecRole = 'Healing')
                OR (SpecName IN ('Feral(Cat)', 'Marksmanship', 'Survival', 'Retribution', 
                'Assassination', 'Outlaw', 'Subtlety', 'Enhancement', 'Arms', 'Fury')
                AND SpecRole = 'Melee Damage')
                OR (SpecName IN ('Beast Mastery', 'Demonology') AND SpecRole = 'Pet')
                OR (SpecName IN ('Balance', 'Arcane', 'Fire', 'Frost', 'Shadow',
                'Elemental', 'Affliction', 'Destruction') AND SpecRole = 'Spell Damage')
                OR (SpecName IN ('Feral(Bear)', 'Protection(Pa)', 'Protection(Wa)') AND SpecRole = 'Tank')),
CONSTRAINT  SpecGroups  CHECK(
                (SpecName IN ('Balance', 'Feral(Bear)', 'Feral(Cat)', 'Restoration(Dr)') AND ClassName = 'Druid')
                OR (SpecName IN ('Beast Mastery', 'Marksmanship', 'Survival') AND ClassName = 'Hunter')
                OR (SpecName IN ('Arcane', 'Fire', 'Frost') AND ClassName = 'Mage')
                OR (SpecName IN ('Holy(Pa)', 'Protection(Pa)', 'Retribution') AND ClassName = 'Paladin')
                OR (SpecName IN ('Discipline', 'Holy(Pr)', 'Shadow') AND ClassName = 'Priest')
                OR (SpecName IN ('Assassination', 'Outlaw', 'Subtlety') AND ClassName = 'Rogue')
                OR (SpecName IN ('Elemental', 'Enhancement', 'Restoration(Sh)') AND ClassName = 'Shaman')
                OR (SpecName IN ('Affliction', 'Demonology', 'Destruction') AND ClassName = 'Warlock')
                OR (SpecName IN ('Arms', 'Fury', 'Protection(Wa)') AND ClassName = 'Warrior')),
 );

And this is my query:

SELECT TOON.ToonName, TOON.CharLevel, TOON.ClassName, TOON.SpecName, 
TOONSPEC.SpecRole, TOONRACE.Faction
FROM TOON
INNER JOIN TOONSPEC ON TOON.ClassName=TOONSPEC.ClassName
INNER JOIN TOONRACE ON TOON.Race=TOONRACE.RaceName
WHERE SpecRole = 'Healing';

EDIT: Here are the query results. I forgot to mention that some of the results are duplicates for some reason, so I’m open to any suggestions on that as well:

I can’t figure out if the problem is with the query or the table structure. Any help would be much appreciated.

Answer

INNER JOIN TOONSPEC ON TOON.ClassName=TOONSPEC.ClassName

You’re storing SpecName in TOON and in TOONSPEC, but you’re not using it when filtering. You need to add an AND to your filtering clause:

INNER JOIN TOONSPEC ON TOON.ClassName=TOONSPEC.ClassName AND TOON.SpecName = TOONSPEC.SpecName

Or, if SpecName are unique among classes, just

INNER JOIN TOONSPEC ON TOON.SpecName = TOONSPEC.SpecName

Advertisement

Answer