返回意外结果的多表SQL查询

I have a table called 'ELEMENTS.' In this query, there should be as many results as there are rows in ELEMENTS.

我有一个名为“元素”的表。在这个查询中，元素中的行数应该和结果数一样多。

The ELEMENT table has numeric values that correspond to text values in other tables. I have drawn this picture that has the relationships. The lines indicate the corresponding keys and the circles are the text values that I need.

元素表具有与其他表中的文本值相对应的数值。我画了这张图，它有关系。线条表示相应的键，圆圈表示我需要的文本值。

Here is my query:

这是我的查询:

SELECT  ELEMENTS.RID,
        TAXONOMIES.SHORT_DESCRIPTION,
        type,
        ELEMENT_NAME,
        ELEMENT_ID,
        SUBSTITUTION_GROUPS.DESCRIPTION,
        namespace_prefix,
        datatype_localname 
FROM ELEMENTS,SUBSTITUTION_GROUPS,TAXONOMIES,SCHEMAS,DATA_TYPES  
WHERE
    ELEMENTS.TAXONOMY_ID = TAXONOMIES.RID AND
    ELEMENTS.SUBSTITUTION_GROUP_ID = SUBSTITUTION_GROUPS.RID AND 
    ELEMENTS.ELEMENT_SCHEMA_ID = SCHEMAS.RID AND
    ELEMENTS.DATA_TYPE_ID = DATA_TYPES.RID

this gives me 20 or so records when I should have thousands. I've looked at the records but can't figure out a pattern in the records it IS showing.

这给了我大约20条记录，而我应该有上千条记录。我看了记录，但不知道它所显示的记录中的模式。

1 个解决方案

#1

Presumably, you dimensions are either NULL or incompletely populated. You can fix this by using left outer join:

您的维度可能是空的，也可能是不完全填充的。你可以使用左外连接来解决这个问题:

SELECT ELEMENTS.RID, TAXONOMIES.SHORT_DESCRIPTION, type, ELEMENT_NAME, ELEMENT_ID, 
       SUBSTITUTION_GROUPS.DESCRIPTION, namespace_prefix, datatype_localname 
FROM ELEMENTS left outer join
     SUBSTITUTION_GROUPS
     on ELEMENTS.SUBSTITUTION_GROUP_ID = SUBSTITUTION_GROUPS.RID left outer join
     TAXONOMIES
     on ELEMENTS.TAXONOMY_ID = TAXONOMIES.RID left outer join
     SCHEMAS
     on ELEMENTS.ELEMENT_SCHEMA_ID = SCHEMAS.RID left outer join
     DATA_TYPES
     on ELEMENTS.DATA_TYPE_ID = DATA_TYPES.RID

If you are learning SQL, you should learn:

如果你正在学习SQL，你应该学习:

1. Proper ANSI standard JOIN syntax
2. 正确的ANSI标准连接语法
3. Prefixing each field in a query with the table where it comes from
4. 在查询中的每个字段前加上它来自的表
5. Using short but understandable aliases, such as "e" for elements and "t" for taxonomy.
6. 使用简短但可理解的别名，如元素的“e”和分类法的“t”。