Restricting Select to exclude a record

Question

0.00/5 (No votes)

See more:

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

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

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.

Posted 14-Oct-12 21:48pm

Franklin Smith

Updated 15-Oct-12 9:00am

Wes Aday

v3

Add a Solution

3 solutions

Solution 1

Try with this code

SQL

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...

Posted 15-Oct-12 0:17am

Divya RS

Updated 15-Oct-12 0:18am

v2

Comments

Franklin Smith 15-Oct-12 14:20pm

This would almost work accept the Orange can not be hard coded and because these are going to put into Stored Procedures I needed similar coding that works both ways. So I came up what you will see below. But I still am having a problem with the one that gives me the results for those that aren't being used.

Solution 4

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

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.

Posted 15-Oct-12 13:34pm

Franklin Smith

Comments

chaau 15-Oct-12 20:17pm

Ok, you asked a question, and you posted the solution to yourself.
You asked a question how to retrieve excluded records, and posting the solution how to retrieve included items.
What exactly did you want?

Franklin Smith 16-Oct-12 3:01am

I found the excluded ones by finding the included ones. It just happens that's how the code worked. I worked on this all day finding the bits and pieces I was missing. It was by no means simple. I just posted what I finally worked out. I like what you posted better as it's more elegant than what I put up.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chaau · Accepted Answer · 2012-10-15T11:09:00

Solution 3

This is what you are after:

SQL

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

Posted 15-Oct-12 11:09am

chaau

Updated 15-Oct-12 11:10am

v2

Comments

Franklin Smith 16-Oct-12 3:18am

Very elegant way of solving the problem. I'm going to have to see if I can implement that into my stored procedure. It would save me the temp tables. However, I'm not sure because one thing I found out I'm going to have to do. I have to add a column in the out table that specifies whether the WordID was found in the WordID Column or the SimilarWordID, but that is a matter for the included side not the exclusion.

One thing I'd like to ask though is, the data that this applies to is actually a subset. The table actually has something like GroupID, Id, Word, Description for columns. I'm just assuming here but to add the GroupID to this I'd need to add a "@GroupID int" to the parameters and in the SELECT's WHERE clause add "GroupID = @GroupID AND"

chaau 16-Oct-12 6:59am

Just remember that SQL allows for a little trick. If you grab a working select statement (no matter how complicated it is) and enclose the whole statement in the brackets it will automatically become a view (or a temp table if you are more confident with them) that can be manipulated further (like joined to other tables, or grouped). You can also assign an alias for this view.

For the example above you can do this:
SELECT * FROM
(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)
) AS A
GROUP BY A.Word
ORDER BY 2

OR you can use the view for joins with other tables:

SELECT * FROM
Table_3,
(
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)
) AS A
WHERE Table_3.Id = A.Id
AND Table_3.GroupId = @GroupId
Order BY Table_3.GroupID

etc. If you need more help with that, just give us more details about your GroupID and we can build a statement for you

Franklin Smith 16-Oct-12 9:49am

The GroupID is actually what I have as a AssessmentID in another table. The "Words" here are vocabulary words for a particular subject and chapter. Because of this in the stored proc I am Selecting A Group of words first into a temp table. But after seeing how you used the view I think I'd rather use them. I used the temp tables because I saw someone elses code use them, but I really hate dealing with the drop statements and IF exist Drop statements cause it is just more overhead. So I'm going to remove those in favor of the views.

So I'm thinking to solve the GroupID problem I need to do the following.
SELECT Id, Word, Description
FROM Table_1
--Changed line here
WHERE GroupID = @GroupID
AND 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)

chaau 16-Oct-12 15:16pm

I just showed you a trick you could use. You do not need to use it in every case, because it may affect the performance when SQL will try to optimise the query. In your case, I think you could just join the tables similar to this:

SELECT Table_3.GroupId, Table_1.Id, Table_1.Word, Table_1.Description
FROM Table_3, Table_1
WHERE
Table_3.GroupID = @GroupID
AND Table_1.GroupID = Table_3.GroupID
AND Table_1.Id <> @ExcludedItem
AND Table_1.Id NOT IN (SELECT SimilarWordID FROM Table_2 WHERE WordId = @ExcludedItem)
AND Table_1.Id NOT IN (SELECT WordId FROM Table_2 WHERE SimilarWordID = @ExcludedItem)
Order BY Table_3.GroupID

Franklin Smith 17-Oct-12 3:07am

After a student logs in, they choose a course to study. The way things come down the line from there is.

Table 1 - CourseID
Table 2 - CourseID,ChapterID
Table 3 - ChapterID,AssessmentID(There can be multiple assessments per chapter not all will be vocab.)
Table 4 - GroupID(AssessmentID), WordID, Word, Description(Maybe better name would have been Definition)
Table 5 - WordID, SimilarWordID

That third table(Table_3) is a generated table that is being returned to a webpage. It doesn't actually exist. This selection of the exclusions is also not the normal. I just need it for the administration section to have a listbox that shows the words not currently linked to WordId.

I went back to check and I have setup Table 4 so that WordId is the Primary Key/Index and the GroupID is indexed. I'm thinking I should remove the indexing on WordId as it seems to me that what I want indexed together would be the GroupID records. I normally would worry about efficiency because in the past most of the stuff I put together was simple one or two concurrent user stuff. But on the student side I will end up with 3-4 hundred concurrent users.

But with regards to the Query would it be better to(I removed description as it is not needed for this Query)

SELECT Id, Word
FROM (SELECT Id, Word FROM Table_1 WHERE GroupID = @GroupID) AS Group
WHERE Group.Id <> @ExcludedItem
AND Group.Id NOT IN (SELECT SimilarWordID FROM Table_2 WHERE WordId = @ExcludedItem) AND Group.Id NOT IN (SELECT WordId FROM Table_2 WHERE SimilarWordID = @ExcludedItem)

Would that be more efficient?