Here are my creation statements:
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))";
My query:
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;
The Result:
<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"><null></td>
<td align="center"><null></td>
</tr>
</table>
The table, feed_item_author, has the correct number of authors and items - 15 authors and 20 items and matches the data properly line for line.
The tables, author and feed_items also have the correct number of rows - 15 authors, and 20 feed_items.
I also have in my code to verify that everything has been entered correctly:
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);
}
What am I missing here? Why does my query return null for the author's name and id, if the author has written more than one article?
Shouldn't you be joining on ON fia.AUTHOR_ID = a.AUTHOR_ID;
You want the author ids to be the same. Unless I'm missing something it makes no sense to want an itemID to match an authorID