I'm fairly new to sql and am probably over my head with this but I keep running into a syntax error in my join statement.

I am trying to get specific stats for a single character. I have added more parenthese to get rid of a missing operator error, and I have tried adding parenthese to only around the inner joins of the same tables. So far the join statement is the only thing throwing errors.

SELECT CHARACTER.CharacterName, CHARACTER.Alignment, INVENTORY.Equipped, 
ITEMS.ItemName,
ITEMS.PhysDef, ITEMS.MDef, ITEMS.Dodge, ITEMS.Damage, ITEMS.CritMultiplier, 
ITEMS.Range,
ITEMS.AttackSpeed, JOB_CHARACTER.JobLevel, RACE_CHARACTER.RacialLevel, 
RACE.RaceName, RACE.Strength,
RACE.Skill, RACE.Vitality, RACE.Arcane, RACE.Spirit, RACE.Charisma, 
RACE.Luck, JOB.JobName,
JOB.HP, JOB.AttackBonus, JOB.Agility, JOB.Might, JOB.SpellPower, JOB.Vital, 
JOB.Nimble, JOB.Mental,
JOB.Curese, JOB.SpellCasting, JOB.ManaBase, JOB.ManaType, JOB.Ki, 
SKILLS.Alchemy, SKILLS.Awareness,
SKILLS.Climb, SKILLS.Coach, SKILLS.Construction, SKILLS.Decieve, 
SKILLS.DisarmMechanism,
SKILLS.DiscernTruth, SKILLS.Dishearten, SKILLS.Fly, SKILLS.Forge, 
SKILLS.Gymnastics,SKILLS.Identify,
SKILLS.Leadership, SKILLS.Lore_9Realms, SKILLS.Lore_Alternative, 
SKILLS.Lore_Arcane,
SKILLS.Lore_Arithmancy, SKILLS.Lore_Divine, SKILLS.Lore_Geography, 
SKILLS.Lore_Nature,
SKILLS.Lore_Nobility, SKILLS.Lore_Religion, SKILLS.Lore_Spiritual, 
SKILLS.Medical, 
SKILLS.Performance, SKILLS.Ride, SKILLS.Steal, SKILLS.Stealth, 
SKILLS.Subterfuge,
SKILLS.Swim, SKILLS.Tailor, SKILLS.UseContraption, SKILLS.Wilderness

FROM (((((((CHARACTER INNER JOIN RACE_CHARACTER ON 
CHARACTER.CharacterID=RACE_CHARACTER.CharacterID)
LEFT JOIN JOB_CHARACTER ON CHARACTER.CharacterID=JOB_CHARACTER.CharacterID)
LEFT JOIN INVENTORY ON CHARACTER.CharacterID=INVENTORY.CharacterID)
INNER JOIN INVENTORY ON ITEMS.ItemID =INVENTORY.ItemID)
INNER JOIN JOB ON JOB.JobID=JOB_CHARACTER.JobID)
LEFT JOIN SKILLS ON JOB.JobID=SKILLS.JobID)
INNER JOIN RACE ON RACE.RaceID=RACE_CHARACTER.RaceID)

WHERE  ((CHARACTER.CharacterID)=1) AND ((JOB.JobID)=3) AND 
((RACE.RaceID)=6);

I expect this to output the stats for this single character, its name, and skills, however it is not currently outputting anything.

1 Answers

0
Lee Mac On Best Solutions

Your select statement is sourcing 8 fields from an ITEMS table:

SELECT 
    ...
    ITEMS.ItemName,
    ITEMS.PhysDef, 
    ITEMS.MDef,
    ITEMS.Dodge,
    ITEMS.Damage, 
    ITEMS.CritMultiplier, 
    ITEMS.Range,
    ITEMS.AttackSpeed,
    ...

However, the ITEMS table is not referenced by your from clause:

FROM 
    (
        (
            (
                (
                    (
                        (
                            (
                                CHARACTER INNER JOIN RACE_CHARACTER ON 
                                CHARACTER.CharacterID=RACE_CHARACTER.CharacterID
                            )
                            LEFT JOIN JOB_CHARACTER ON 
                            CHARACTER.CharacterID=JOB_CHARACTER.CharacterID
                        )
                        LEFT JOIN INVENTORY ON 
                        CHARACTER.CharacterID=INVENTORY.CharacterID
                    )
                    INNER JOIN INVENTORY ON ----------< INVENTORY table referenced twice
                    ITEMS.ItemID =INVENTORY.ItemID
                )
                INNER JOIN JOB ON 
                JOB.JobID=JOB_CHARACTER.JobID
            )
            LEFT JOIN SKILLS ON 
            JOB.JobID=SKILLS.JobID
        )
        INNER JOIN RACE ON 
        RACE.RaceID=RACE_CHARACTER.RaceID
    )

I should imagine the SQL code should be changed to something like:

SELECT 
    CHARACTER.CharacterName, 
    CHARACTER.Alignment, 
    INVENTORY.Equipped, 
    ITEMS.ItemName,
    ITEMS.PhysDef, 
    ITEMS.MDef,
    ITEMS.Dodge,
    ITEMS.Damage, 
    ITEMS.CritMultiplier, 
    ITEMS.Range,
    ITEMS.AttackSpeed, 
    JOB_CHARACTER.JobLevel,
    RACE_CHARACTER.RacialLevel, 
    RACE.RaceName, 
    RACE.Strength,
    RACE.Skill,
    RACE.Vitality, 
    RACE.Arcane, 
    RACE.Spirit, 
    RACE.Charisma, 
    RACE.Luck,
    JOB.JobName,
    JOB.HP, 
    JOB.AttackBonus, 
    JOB.Agility, 
    JOB.Might, 
    JOB.SpellPower,
    JOB.Vital, 
    JOB.Nimble, 
    JOB.Mental,
    JOB.Curese, 
    JOB.SpellCasting,
    JOB.ManaBase, 
    JOB.ManaType, 
    JOB.Ki, 
    SKILLS.Alchemy, 
    SKILLS.Awareness,
    SKILLS.Climb, 
    SKILLS.Coach, 
    SKILLS.Construction, 
    SKILLS.Decieve, 
    SKILLS.DisarmMechanism,
    SKILLS.DiscernTruth, 
    SKILLS.Dishearten, 
    SKILLS.Fly, 
    SKILLS.Forge, 
    SKILLS.Gymnastics,
    SKILLS.Identify,
    SKILLS.Leadership, 
    SKILLS.Lore_9Realms, 
    SKILLS.Lore_Alternative, 
    SKILLS.Lore_Arcane,
    SKILLS.Lore_Arithmancy, 
    SKILLS.Lore_Divine, 
    SKILLS.Lore_Geography, 
    SKILLS.Lore_Nature,
    SKILLS.Lore_Nobility, 
    SKILLS.Lore_Religion, 
    SKILLS.Lore_Spiritual, 
    SKILLS.Medical, 
    SKILLS.Performance, 
    SKILLS.Ride, 
    SKILLS.Steal, 
    SKILLS.Stealth, 
    SKILLS.Subterfuge,
    SKILLS.Swim, 
    SKILLS.Tailor, 
    SKILLS.UseContraption, 
    SKILLS.Wilderness
FROM 
    (
        (
            (
                (
                    (
                        (
                            (
                                CHARACTER INNER JOIN RACE_CHARACTER ON 
                                CHARACTER.CharacterID=RACE_CHARACTER.CharacterID
                            )
                            LEFT JOIN JOB_CHARACTER ON 
                            CHARACTER.CharacterID=JOB_CHARACTER.CharacterID
                        )
                        LEFT JOIN INVENTORY ON 
                        CHARACTER.CharacterID=INVENTORY.CharacterID
                    )
                    LEFT JOIN ITEMS ON 
                    ITEMS.ItemID =INVENTORY.ItemID
                )
                LEFT JOIN JOB ON 
                JOB.JobID=JOB_CHARACTER.JobID
            )
            LEFT JOIN SKILLS ON 
            JOB.JobID=SKILLS.JobID
        )
        INNER JOIN RACE ON 
        RACE.RaceID=RACE_CHARACTER.RaceID
    )
WHERE 
    CHARACTER.CharacterID = 1 AND
    JOB.JobID = 3 AND 
    RACE.RaceID = 6

Note that I have changed a couple of the inner joins to left joins because if you use an inner join on a table which is the right of a left join (or on the left of a right join), then you will receive an ambiguous outer joins error.