sql - 如果作者写了不止一篇文章，为什么这个SQL查询将文章的作者名返回NULL？

Question

这是我的创作陈述:

            String createFeedTable = "CREATE TABLE  rss_feed ("
                + "channel_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + "channelTitle VARCHAR(255), "
                + "channelLink VARCHAR(255), "
                + "channelDescription VARCHAR(255), "
                + "channelPubDate TIMESTAMP, "
                + "channelLastBuildDate TIMESTAMP,"
                + "channelIcon BLOB(1M),"
                + "PRIMARY KEY (channel_ID))";

        String createFeedItemTable = "CREATE TABLE  feed_item ("
                + "  item_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                + "  itemTitle VARCHAR(255),"
                + "  itemLink VARCHAR(255),"
                + "  itemDescription VARCHAR(255),"
                + "  itemGuid VARCHAR(255),"
                + "  itemPubDate TIMESTAMP,"
                + "  channel_ID INTEGER REFERENCES rss_feed(channel_ID),"
                + "  haveRead BOOLEAN DEFAULT FALSE,"
                + "  PRIMARY KEY (item_ID))";

        String createCategoryTable = "CREATE TABLE  category ("
                + "category_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                + "categoryName VARCHAR(255), "
                + "CONSTRAINT category_constraint UNIQUE (categoryName),"
                + "PRIMARY KEY (category_ID))";

        String createFeedItemCategoryTable = "CREATE TABLE  feed_item_category ("
                + "  item_ID INTEGER REFERENCES feed_item(item_ID),"
                + "  category_ID INTEGER REFERENCES category(category_ID))";

        String createFeedCategoryTable = "CREATE TABLE  rss_feed_category ("
                + "feed_ID INTEGER REFERENCES rss_feed(channel_ID),"
                + "category_ID INTEGER REFERENCES category(category_ID))";

        String createAuthorTable = "CREATE TABLE  author ("
                + "author_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                + "authorName VARCHAR(255),"
                + "CONSTRAINT author_constraint UNIQUE (authorName),"
                + "PRIMARY KEY (author_ID))";

        String createFeedItemAuthorTable = "CREATE TABLE  feed_item_author ("
                + "item_ID INTEGER REFERENCES feed_item(item_ID),"
                + "author_ID INTEGER REFERENCES author(author_ID))";

我的查询:

SELECT i.ITEMTITLE, i.ITEMLINK, i.ITEMDESCRIPTION, i.ITEMPUBDATE, i.CHANNEL_ID,
  i.ITEM_ID, a.AUTHOR_ID, a.AUTHORNAME

    FROM FEED_ITEM i
    LEFT JOIN FEED_ITEM_AUTHOR fia
         ON i.ITEM_ID = fia.ITEM_ID
    LEFT JOIN AUTHOR a
         ON fia.ITEM_ID = a.AUTHOR_ID;

结果:

<table border =1>
<tr>  
<th>ITEMTITLE</th>
<th>ITEM_ID</th>
<th>AUTHOR_ID</th>
<th>AUTHORNAME</th>
</tr>
<tr>
<td align="center">Lawyers...</td>
<td align="center">1</td>
<td align="center">1</td>
<td align="center">Joe Mullin</td>
</tr>
<tr>
<td align="center">AT&T/Time Warner...</td>
<td align="center">2</td>
<td align="center">2</td>
<td align="center">Jon Brodkin</td>
</tr>
<tr>
<td align="center">The “technosphere” ...</td>
<td align="center">19</td>
<td align="center">19</td>
<td align="center">Annalee Newitz</td>
</tr>
  <tr>
<td align="center">Decrypted: ...</td>
<td align="center">20</td>
<td align="center">&lt;null&gt;</td>
<td align="center">&lt;null&gt;</td>
</tr>


</table>

表格 feed_item_author 具有正确的作者和项目数-15个作者和20个项目，并且正确地匹配了每一行的数据。

表格 的作者和 feed_items 的行数也正确-15个作者和20个feed_items。

我的代码中还包含验证所有输入是否正确的代码:

  try {

        feedItem_AuthorStatement = conn.prepareStatement(feed_item_author_SQL, PreparedStatement.RETURN_GENERATED_KEYS);

        feedItem_AuthorStatement.setInt(1, item_ID);
        feedItem_AuthorStatement.setInt(2, author_ID);
        int entrySuccess = feedItem_AuthorStatement.executeUpdate();

        System.out.println("SUCCESS? :" + entrySuccess);
    } catch (SQLException ex) {
        Logger.getLogger(FeedItemCategoryHelper.class.getName()).log(Level.SEVERE, null, ex);
    }

我在这里想念什么？如果作者写了多篇文章，为什么我的查询返回的作者姓名和ID为null？

Answer 1

您不应该加入ON fia.AUTHOR_ID = a.AUTHOR_ID;

您希望作者ID相同。除非我缺少什么，否则想要itemID匹配authorID毫无意义。