Suppose I have two tables, one with blog posts and another with readers and their comments:
Table 1:
table name: BlogPosts:
structure:
id (int)
title (string)
Table 2:
table name: Readers:
id (int)
blog_post_id (int)
name (string)
comment (string)
in the Readers table there is a unique composite key on blog_post_id/name (i.e. one comment per reader per post), though this may not matter for my question.
I want to be able to do a single query tells me what a particular reader's comment was on each BlogPost, but it should include BlogPosts for which there is no comment entered for that reader (so the query should return one row for each blog post in the database).
I tried several variations that look like this:
SELECT
BlogPosts.id,
BlogPosts.title,
Readers.name,
Readers.comment
FROM
BlogPosts
RIGHT JOIN Readers ON
(BlogPosts.id = Readers.blog_post_id)
WHERE Readers.name = "joe"
..this just returns the rows where there is actually a comment from joe. Other variations where I was able to get all of the blog posts gave me an invalid identifier whenever I included the where clause.
I am using Oracle Express 10g in case that makes any difference.
Thanks for any help.