我的联接操作中有语法错误的快速修复吗？

Question

我对sql相当陌生，我可能对此感到头脑发热，但我在join语句中始终遇到语法错误。

我试图得到一个单一字符的具体统计数据。为了消除缺少的操作符错误，我添加了更多的括号，并且我尝试将括号添加到相同表的内部联接周围。到目前为止，join语句是唯一抛出错误的东西。

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);

我希望这将输出这个单一字符的统计数据，它的名称和技能，但它目前没有输出任何东西。

Answer 1

您的select语句正在从一个ITEMS表中获取8个字段：

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

但是，ITEMS表是而不是由from子句引用的：

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
    )

我应该设想将SQL代码更改为如下所示：

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

请注意，我已经将两个inner joins更改为left joins，因为如果您在一个表(即left join的右侧或right join的左侧)上使用inner join，那么您将收到一个模糊的外部联接错误。