如果有匹配的话(Where if there's a match)

如果有匹配的话(Where if there's a match)

我有一个我正在选择的表，它从登录中记录“签名”。

表就像这样：

| Int| VARCHAR | Guid | Bit | Int | NVARCHAR(MAX) | ID | UserName | UserId | Signed | Rec | additional info etc... | -- | -------- | ------ | ------ | --- | --------------------- | 1 | Bob | 12 | 0 | 100 | sasdd | 2 | Steve | 14 | 1 | 100 | asretg | 3 | GROUP: 2 | 76 | 0 | 101 | This is a group of logins | 4 | Bob | 12 | 1 | 101 | asdfasd

因此，第5列是其签名的目标ID。

为特定用户（Bob）构建未签名项目列表非常简单：

SELECT Rec FROM tbl_Sigs WHERE Signed = 0 And UserId = '12'

现在，我已经将登录组添加到此列表中，如第3项所示 - 在此示例中，该组同时具有两个登录名称，并且我能够在代码中从登录中提取，它可以访问哪个组，因此声明变成：

SELECT Rec FROM tbl_Sigs WHERE (Signed = 0 And UserId = '12') OR UserId IN (76,77,78)

这样可行，但在示例中将选择记录100和101 。

我想要的只是为Bob选择记录100 ，因为我有101匹配，因为“GROUP：2”（总是无符号），但是“Bob”已经签署了记录101 ，因此不需要它在他的名单中。

我无法弄清楚如果将该条件放入where子句，我可以在C＃中使用数据集，但如果可能的话，我更喜欢纯SQL方式。

I have a table I'm selecting from, which logs "Signatures" from logins.

The table is like so:

| Int| VARCHAR | Guid | Bit | Int | NVARCHAR(MAX) | ID | UserName | UserId | Signed | Rec | additional info etc... | -- | -------- | ------ | ------ | --- | --------------------- | 1 | Bob | 12 | 0 | 100 | sasdd | 2 | Steve | 14 | 1 | 100 | asretg | 3 | GROUP: 2 | 76 | 0 | 101 | This is a group of logins | 4 | Bob | 12 | 1 | 101 | asdfasd

So column 5 is the target ID to which it's been signed.

To build a list of unsigned items for a specific user (Bob) was pretty straight forward:

SELECT Rec FROM tbl_Sigs WHERE Signed = 0 And UserId = '12'

Now, I've added login groups to this list, as in item 3 - In this example, the group has both logins in it and I'm able in code to pull from the login, which group it has access to, so the statement becomes:

SELECT Rec FROM tbl_Sigs WHERE (Signed = 0 And UserId = '12') OR UserId IN (76,77,78)

This works, but in the example will select record 100 and 101.

What I would like is to only select record 100 for Bob, because I have a match for 101 because of "GROUP: 2" (Which will always be unsigned), however "Bob" has signed record 101 already, hence doesn't need it in his list.

What I can't figure out if how to put that condition into the where clause, I could do it in C# once I have the dataset but would prefer a pure SQL way if possible.

最满意答案

我想你只需要一个NOT EXISTS条款？

DECLARE @tbl_Sigs TABLE ( ID INT, UserName VARCHAR(50), UserId INT, --Not a GUID! Signed BIT, Rec INT, AdditionalInfo VARCHAR(MAX)); INSERT INTO @tbl_Sigs VALUES (1, 'Bob', 12, 0, 100, 'sasdd'); INSERT INTO @tbl_Sigs VALUES (2, 'Steve', 14, 1, 100, 'asretg'); INSERT INTO @tbl_Sigs VALUES (3, 'GROUP: 2', 76, 0, 101, 'This is a group of logins'); INSERT INTO @tbl_Sigs VALUES (4, 'Bob', 12, 1, 101, 'asdfasd'); --So column 5 is the target ID to which it's been signed. --To build a list of unsigned items for a specific user (Bob) was pretty straight forward: SELECT Rec FROM @tbl_Sigs WHERE Signed = 0 And UserId = '12'; --Now, I've added login groups to this list, as in item 3 - In this example, the group has both logins in it and I'm able in code to pull from the login, which group it has access to, so the statement becomes: SELECT Rec FROM @tbl_Sigs r1 WHERE (Signed = 0 And UserId = '12') OR UserId IN (76, 77, 78) AND NOT EXISTS (SELECT * FROM @tbl_Sigs r2 WHERE r2.Rec = r1.Rec AND r2.UserId = '12' AND r2.Signed = 1);

I think you just need a NOT EXISTS clause?

DECLARE @tbl_Sigs TABLE ( ID INT, UserName VARCHAR(50), UserId INT, --Not a GUID! Signed BIT, Rec INT, AdditionalInfo VARCHAR(MAX)); INSERT INTO @tbl_Sigs VALUES (1, 'Bob', 12, 0, 100, 'sasdd'); INSERT INTO @tbl_Sigs VALUES (2, 'Steve', 14, 1, 100, 'asretg'); INSERT INTO @tbl_Sigs VALUES (3, 'GROUP: 2', 76, 0, 101, 'This is a group of logins'); INSERT INTO @tbl_Sigs VALUES (4, 'Bob', 12, 1, 101, 'asdfasd'); --So column 5 is the target ID to which it's been signed. --To build a list of unsigned items for a specific user (Bob) was pretty straight forward: SELECT Rec FROM @tbl_Sigs WHERE Signed = 0 And UserId = '12'; --Now, I've added login groups to this list, as in item 3 - In this example, the group has both logins in it and I'm able in code to pull from the login, which group it has access to, so the statement becomes: SELECT Rec FROM @tbl_Sigs r1 WHERE (Signed = 0 And UserId = '12') OR UserId IN (76, 77, 78) AND NOT EXISTS (SELECT * FROM @tbl_Sigs r2 WHERE r2.Rec = r1.Rec AND r2.UserId = '12' AND r2.Signed = 1);