Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

sql - Join four tables involving LEFT JOIN without duplicates

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...