具有多个条件的T-SQL和/或LINQ查询

I have the following sample many-to-many relationship:

我有以下示例多对多关系：

I am building a UI to allow a user to select multiple Documents to be batched together. Each Batch can have many Documents; each Document can only be in one Batch UNLESS the Batch has been voided.

我正在构建一个UI，允许用户选择多个文档进行批处理。每批可以有很多文件;每个文档只能在一个批处理中，除非批处理无效。

On this UI, I need to display available Documents. A Document is considered available if it is:

在此UI上，我需要显示可用的文档。如果文件是：

Not void, not batched (i.e. has a record in BatchDocument to associated it to a Batch)
不是空的，不是批处理的（即在BatchDocument中有一条记录将它与批处理相关联）
Not void, batched BUT the Batch is void
不是无效，批处理但批处理无效

Sample data:

样本数据：

CREATE TABLE tmpDocument (DocumentID INT, IsVoid BIT)

INSERT INTO tmpDocument VALUES (1,1)
INSERT INTO tmpDocument VALUES (2,0)
INSERT INTO tmpDocument VALUES (3,0)
INSERT INTO tmpDocument VALUES (4,0)
INSERT INTO tmpDocument VALUES (5,0)
INSERT INTO tmpDocument VALUES (6,0)
INSERT INTO tmpDocument VALUES (7,0)
INSERT INTO tmpDocument VALUES (8,0)
INSERT INTO tmpDocument VALUES (9,0)

CREATE TABLE tmpBatch (BatchID INT, IsVoid BIT)

INSERT INTO tmpBatch VALUES (1,0)
INSERT INTO tmpBatch VALUES (2,1)
INSERT INTO tmpBatch VALUES (3,0)

CREATE TABLE tmpBatchDocument (BatchDocumentID INT, BatchID INT, DocumentID INT)

INSERT INTO tmpBatchDocument VALUES (1,1,2)
INSERT INTO tmpBatchDocument VALUES (2,1,3)
INSERT INTO tmpBatchDocument VALUES (3,2,4)
INSERT INTO tmpBatchDocument VALUES (4,2,5)
INSERT INTO tmpBatchDocument VALUES (5,3,6)
INSERT INTO tmpBatchDocument VALUES (6,3,7)
INSERT INTO tmpBatchDocument VALUES (7,3,8)

Document

文件

DocumentID---IsVoid
1------------1-----
2------------0-----
3------------0-----
4------------0-----
5------------0-----
6------------0-----
7------------0-----
8------------0-----
9------------0-----

Batch

批量

BatchID------IsVoid
1------------0-----
2------------1-----
3------------0-----

BatchDocument

BatchDocument

BatchDocumentID-----BatchID------DocumentID
1-------------------1------------2---------
2-------------------1------------3---------
3-------------------2------------4---------
4-------------------2------------5---------
5-------------------3------------6---------
6-------------------3------------7---------
7-------------------3------------8---------

To find available Documents, I came up with this T-SQL query:

为了找到可用的文档，我提出了这个T-SQL查询：

SELECT a.DocumentID
FROM tmpDocument a
LEFT JOIN tmpBatchDocument b ON a.DocumentID = b.DocumentID
LEFT JOIN tmpBatch c ON b.BatchID = c.BatchID
WHERE (
        b.DocumentID IS NULL
        AND a.IsVoid = 0
        )
    OR (c.IsVoid = 1)

The query correctly returns:

查询正确返回：

DocumentID
4
5
9

Document 1 is excluded because it is voided; Documents 2, 3, 6, 7, and 8 are excluded because they are batched and the Batch is not voided; Documents 4 and 5 are included because although they are batched, the Batch has been voided; Document 9 is included because it is not voided and it is not batched

文件1被排除在外，因为它无效;文档2,3,6,7和8被排除在外，因为它们是批处理的，批处理没有空白;包括文档4和5，因为虽然它们是批处理的，但批处理已经无效;包含文件9是因为它没有空白而且没有批量处理

Now, let's say I select Documents 4 and 5 and add them to a new Batch.

现在，假设我选择了文档4和5，并将它们添加到新批处理中。

BatchID------IsVoid
1------------0-----
2------------1-----
3------------0-----
4------------0-----

BatchDocumentID-----BatchID------DocumentID
1-------------------1------------2---------
2-------------------1------------3---------
3-------------------2------------4---------
4-------------------2------------5---------
5-------------------3------------6---------
6-------------------3------------7---------
7-------------------3------------8---------  
8-------------------4------------4---------
9-------------------4------------5---------

Running the same query returns the same results:

运行相同的查询会返回相同的结果：

DocumentID
4
5
9

Since Documents 4 and 5 are now associated to a non-voided Batch, I expected they would be excluded from the results and only Document 9 would be shown. Clearly, that did not happen.

由于文件4和5现在与非无效批次相关联，我预计它们将被排除在结果之外，并且仅显示文件9。显然，这没有发生。

How can I rewrite this query to show Documents that are only associated to a voided batch, but exclude them if they are also associated to a non-voided Batch?

如何重写此查询以显示仅与无效批次关联的文档，但如果它们也与无效批处理相关联，则将其排除？

2 个解决方案

#1

Using COUNT and HAVING:

使用COUNT和HAVING：

SELECT d.DocumentID
FROM tmpDocument d
LEFT JOIN tmpBatchDocument bd 
    ON bd.DocumentID = d.DocumentID
LEFT JOIN tmpBatch b
    ON b.BatchID = bd.BatchID
WHERE
    d.IsVoid = 0
GROUP BY d.DocumentID
HAVING 
    COUNT(bd.BatchDocumentID) = 0
    OR COUNT(CASE WHEN b.IsVoid = 0 THEN 1 END) = 0

The WHERE clause will obviously exclude voided documents. The first condition in the HAVING clause searches for documents that are not batched and the second condition will search for documents that are batch but the batch is voided.

WHERE子句显然会排除无效文档。 HAVING子句中的第一个条件搜索未批处理的文档，第二个条件将搜索批处理但批处理无效的文档。

Try it here.

试试吧。

#2

SELECT a.DocumentID
FROM tmpDocument a
LEFT JOIN tmpBatchDocument b ON a.DocumentID = b.DocumentID
LEFT JOIN tmpBatch c ON b.BatchID = c.BatchID
WHERE (
        b.DocumentID IS NULL
        AND a.IsVoid = 0 --WHY?  
        )
    OR (c.IsVoid = 1
        AND NOT EXISTS (select * from c2 where c2.batchID = (select batchID from b2 where b2.DocumentID=a.documentID) AND c2.isVoid=0)
)

Edited answer to include the NOT EXISTS. Having now read your full question, that's what I think you need in order to exclude those records where there is a non-void batch associated to the document.

编辑答案包括NOT EXISTS。现在已经阅读了您的完整问题，这就是我认为您需要排除那些与文档关联的非空批处理的记录。

#1