sql - Join four tables involving LEFT JOIN without duplicates

Question

I want to join four tables with null values and without duplicates and afterwards translate it into an SQLAlchemy query.

The tables are (simplified):

Category(id, name)
Task(id, category.id, name)
User(id, name)

And an many-to-many table:

Solved(task.id, user.id)

I want to get all tasks with their category and a column with an specific user who solved the task:

+---------------+-----------+-----------+
| category.name | task.name | user.name |
+---------------+-----------+-----------+
| abc           | abctask1  | <null>    |
| abc           | abctask2  | luke      |
| def           | deftask1  | <null>    |
| ghi           | ghitask1  | <null>    |
| ghi           | ghitask2  | luke      |
+---------------+-----------+-----------+

At the moment I've got 3 to 4 individual SQLAlchemy-queries to perform that task. If possible, it should be merged into only one query to avoid too many reads on the database.

So far I've got:

SELECT DISTINCT
  cat.name, t.name, u.name
FROM
  Task t
JOIN 
  Category cat ON cat.id = t.category_id
LEFT JOIN 
  Solved s ON s.task_id = t.id
LEFT JOIN 
  User u ON s.user_id = u.id AND
  u.name = 'luke'
ORDER BY
  cat.name

But, although DISTINCT, I got duplicates from all rows with the given user:

+---------------+-----------+-----------+
| category.name | task.name | user.name |
+---------------+-----------+-----------+
| abc           | abctask1  | <null>    |
| abc           | abctask2  | luke      |
| abc           | abctask2  | <null>    | <-- duplicate
| def           | deftask1  | <null>    |
| ghi           | ghitask1  | <null>    |
| ghi           | ghitask2  | luke      |
| ghi           | ghitask2  | <null>    | <-- duplicate
+---------------+-----------+-----------+

Is there a possibility to get this table with one query and translate it to SQLAlchemy?

Answer 1

The problem comes from your data, ie you probably have 2 task called abctask2/ ghitask2. Maybe you should put a constraint on the taskname. You query works well.

http://sqlfiddle.com/#!9/c4647c/4

Try to check with

SELECT category_id, name ,count(*) from TASK GROUP BY category_id, name HAVING COUNT(*)<>1