如何使用软删除选择多对多关系中不相关的数据？

We have data in a many-to-many relationship, where each of our members has can select the subjects that they are studying. This data makes use of a soft-delete pattern, where a DATETIME archived field remains NULL until such a time that the data is considered deleted.

我们拥有多对多关系中的数据,我们的每个成员都可以选择他们正在学习的科目。此数据使用软删除模式,其中DATETIME存档字段保持为NULL,直到数据被视为已删除为止。

What we want to do is select subjects that the user is not currently subscribed to, ignoring subjects they are currently subscribed to.

我们想要做的是选择用户当前未订阅的主题,忽略他们当前订阅的主题。

This is the table structure:

这是表结构:

`member`
- id
- name
- created
- updated
- archived

`subject`
- id
- name
- created
- updated
- archived

`member_subject`
- member_id
- subject_id
- created
- archived

The basic query structure we have is:

我们的基本查询结构是:

SELECT DISTINCT subject.*
FROM subject
LEFT JOIN member_subject ON member_subject.subject_id = subject.id
WHERE subject.archived IS NULL

# Not sure how to go about the rest of the WHERE clause here

ORDER BY subject.name

2 个解决方案

#1

Try this query -

试试这个查询 -

SELECT s.id
FROM subject s
LEFT JOIN member_subject ms
  ON ms.subject_id = s.id
GROUP BY s.id
HAVING COUNT(ms.subject_id) = 0

...add additional WHERE filter you need.

...添加您需要的其他WHERE过滤器。

#2

this will generate all subjects for each user that is currently not being subscribed,

这将生成当前未订阅的每个用户的所有主题,

SELECT  d.*, e.*
FROM    
        (
            SELECT  a.ID memberID, b.ID SubjectID
            FROM    member a, subject b
        ) c
        INNER JOIN member d
            ON c.memberID = d.ID
        INNER JOIN Subject e
            ON c.SubjectID = e.ID
        LEFT JOIN member_subject f
            ON  c.memberID = f.member_ID AND
                c.SubjectID = f.subject_ID
WHERE   f.member_ID IS NULL
        -- AND d.ID = ''                        -- <<== for specific userID

#1