I'm trying to create query with user_id parameter that would select one row per article_id where row with set user_id has priority. For example:
If user_id = 1, rows with id 2, 3, 4 should be selected.
If user_id = 2, rows with id 1, 3, 5 should be selected.
If user_id = 17, rows with id 1, 3, 4 should be selected.
Please consider pair (user_id, article_id) unique.
id user_id article_id
1 null 8
2 1 8
3 null 9
4 null 10
5 2 10
You can use this query:
SELECT *
FROM articles a1
WHERE user_id = 1 OR
user_id IS NULL AND NOT EXISTS(SELECT * FROM articles a2 WHERE a2.article_id = a1.article_id AND a2.user_id = 1)
ORDER BY user_id IS NULL
This will find all the articles which match user_id = 1, or a NULL user_id where there was not a matching entry with the requested user_id. The results are ordered by user_id IS NULL which will be 0 for an article which matches the user_id, and 1 for an article where user_id is NULL, thus prioritising the actual matches on user_id.
Output (for user_id=1):
id user_id article_id
2 1 8
3 (null) 9
4 (null) 10
Output (for user_id=2):
id user_id article_id
5 2 10
1 (null) 8
3 (null) 9
Output (for user_id=17):
id user_id article_id
1 (null) 8
3 (null) 9
4 (null) 10
