限制选择以排除记录 [英] Restricting Select to exclude a record

问题描述

可以说我有下表

Lets say I have the following table

Id, Word, Description

1, apple, blah blah blah
2, banana, blah blah blah
3, orange, blah blah blah
4, plum, blah blah blah
5, peach, blah blah blah
6, grapes, blah blah blah

在第二张表中，我们有

In a second table we have

WordID, SimilarWordID
3,4
3,5
1,3

如果选择了橙色，那么我需要进行什么查询才能拥有第三个表，该表不包含任何Word，而无论

If orange was selected, what would I need query wise to have a third table that excludes any Word regardless of

Id, Word
2, banana
6, grapes

我的意思是我可以轻松地从程序中手动进行过滤，但是如果可以简单地"执行操作，我宁愿在SQL中完成所有操作.

I mean I could easily do the filtering manually from the program, but I''d prefer to do it all in SQL if it is possible to "simply" do.

推荐答案

这就是您所追求的:

This is what you are after:

CREATE PROCEDURE GiveMeAllBut 
   @ExcludedItem int
AS
   SET NOCOUNT ON;
   SELECT Id, Word, Description 
   FROM Table_1
   WHERE Id <> @ExcludedItem
   AND Id NOT IN (SELECT SimilarWordID FROM Table_2 WHERE WordId = @ExcludedItem)
   AND Id NOT IN (SELECT WordId FROM Table_2 WHERE SimilarWordID = @ExcludedItem)
GO

让我知道您是否想要通过传递水果名称来工作的版本

Let me know if you want a version that works by passing the fruit name

尝试使用此代码

Try with this code

Insert into Table_3
select id, word from Table_1 where id not in(
select  similarwordid from Table_2 where wordid=(
select id from Table_1 where word='orange')union
select wordid from Table_2 where similarwordid=(
select id from Table_1 where word='orange')union
select id from Table_1 where word='orange')
select * from Table_3

让我知道这是否有帮助...

and let me know if this helps or not...

IF OBJECT_ID(''tempdb..#temp3'') IS NOT NULL
	DROP TABLE #temp3
GO
declare @Id smallint;
Set @Id = 3;
CREATE TABLE #temp3(
	[Id] [smallint],
	[Word] [nvarchar](50)
	)
INSERT INTO #temp3 (Id)
SELECT DISTINCT
CASE @Id 
WHEN SimilarWords.WordID THEN SimilarWords.SimilarWordID
WHEN SimilarWords.SimilarWordID THEN SimilarWords.WordID
END
FROM            SimilarWords 
WHERE        (SimilarWords.WordID = @Id) OR
                         (SimilarWords.SimilarWordID = @Id) 
GO
declare @Id smallint;
Set @Id = 3;
INSERT INTO  #temp3 (Id)
VALUES (@Id)
UPDATE #temp3 
SET Word = (SELECT VocabDB.Word FROM VocabDB WHERE #temp3.Id = VocabDB.Id)
GO
SELECT * FROM #temp3 ORDER BY #temp3.Word
-- SELECT DISTINCT Id, Word FROM VocabDB WHERE (VocabDB.Id != #temp3.Id)
-- Gives the error "The multi-part identifier "#temp3.Test" could not be bound."

 
--SELECT DISTINCT VocabDB.Id, VocabDB.Word FROM VocabDB LEFT JOIN #temp3 ON VocabDB.Id = #temp3.Id WHERE #temp3.Id IS NULL
-- After much searching I finally found something that helped me with the above line of coding. 

GO
DROP TABLE #temp3
GO

作为存储过程插入时，我删除了许多不必要的代码，但这对于我所需的内容来说是完美的.除了未使用的单词外，没有必要将@Id插入到临时表中.

I removed a lot of the unnecessary code when I inserted as stored procedures, but this worked flawlessly for what I needed. The Insert of the @Id in to the temp table is not necessary except during the the Unused Words.

这篇关于限制选择以排除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！