MySQL Left Outer Join with Absence Condition

Question

Overview

I'm simplifying the description of my application to make the query clearer. The application stores saved phrases. These phrases are each assigned a category. The phrases can be posted by users. An English description of my query:

I need to pass a category and user into the prepared statement. The result will include only phrases with the category passed as an argument. From the phrases of this category, only phrases will be returned where there does not exist any posted instances of this phrase for the user argument with the timePosted field in the last two weeks.

Minimal Schema

savedPhrase

• phraseIdentifier - INT UNSIGNED
• textContent - VARCHAR
• associatedCategory - VARCHAR
• ___primary key is phraseIdentifier

postedPhrase

• savedPhrase - INT UNSIGNED - foreign key references savedPhrase(phraseIdentifier)
• username - VARCHAR
• timeReposted - TIMESTAMP
• ___primary key is combination of all listed fields

Current Best Attempt with Behavior

I'm self-taught/learning MySQL and believe I struggle with joins. I think I should be using at least one left outer join as seen in the graphic below. The left table will be savedPhrase and the right will be postedPhrase.

SELECT * FROM savedPhrase S 
LEFT JOIN postedPhrase P
ON (S.phraseIdentifier = P.savedPhrase AND P.username = ?)
WHERE
(P.savedPhrase IS NULL OR P.timeReposted < DATE_SUB(NOW(), INTERVAL 14 day)) AND
S.associatedCategory = ?

The query above ALMOST works. However, once a single postedPhrase row exists at least two weeks ago, it will return the joined savedPhrase, even if one has been posted on the same account with the same identifier less than two weeks ago.

Further Discussion

The place where I'm having difficulty is the "absence condition". This is where a savedPhrase must not exist within the last two weeks. In the previous paragraph I explained how the absence is not functioning because the postedPhrases are being returned based on their own independent timeReposted. It seems I should be adding the absence condition to the right circle of the left outer join. Simplification of when to apply the conditions to the ON versus the WHERE would be very helpful.

I've worked at fixing this problem for a while and would appreciate any direction. Please let me know if I can include any other information.

Answer 1

You don't have to to use JOIN to archive the result that you want. You could write the query base on the English description of the query which described on your question.

SELECT S.* 
FROM   savedPhrase S 
WHERE  S.associatedCategory = ? 
       AND NOT EXISTS (SELECT 1 
                       FROM   postedPhrase P 
                       WHERE  P.username = ? 
                              AND S.phraseIdentifier = P.savedPhrase 
                              AND P.timeReposted >= DATE_SUB(NOW(), 
                                                    INTERVAL 14 day))