Exclude records using a subquery in SQL SERVER

Question

4.43/5 (3 votes)

See more:

Hi,

I am not an expert in SQL.I have a table where data entered in a wrong patter.I have to exclude some records( i can't delete) i am trying to create a view.
Tried with sub queries but not worked for me

here is my data

HTML

stid	Ayear	Aterm	Award	Details
43	2014	fall      		
43	2015	fall      HONOR	   Honor List
43	2015	spring    	      good
43	2016	spring    	      good
43	2016	spring    HONOR	   Honor List
45	2014	fall      		
45	2015	fall      PRESIDENT PresidentList
45	2015	spring    	      good
45	2016	spring    	      good
45	2016	spring   HONOR	   Honor List

this is my data.i want to exclude the row with details 'Good' when there is Honor list in the same year and term

for Student 45 in 2016 spring we have good and honorlist
in this case we need to remove the record with 'good'
and keep the honorlist as such

This is the table structure

SQL

CREATE TABLE [dbo].[testawards](
	[stid] [int] NULL,
	[Ayear] [int] NULL,
	[Aterm] [nchar](10) NULL,
	[Award] [nvarchar](50) NULL,
	[Details] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sample data

SQL

insert into testawards  (stid,Ayear,Aterm,Award,Details) values (43,2014,'fall','','')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (43,2015,'fall','HONOR','Honor List')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (43,2015,'spring','','good')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (43,2016,'spring','','good')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (43,2016,'spring','HONOR','Honor List')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (45,2014,'fall','','')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (45,2015,'fall','PRESIDENT','PresidentList')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (45,2015,'spring','','good')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (45,2016,'spring','','good')
insert into testawards  (stid,Ayear,Aterm,Award,Details) values (45,2016,'spring','HONOR','Honor List')

What I have tried:

I tried with Subqueries but not worked .
just took the count of records with same term and year then if there is Honor ,but this is not worked for me

Posted 7-Sep-16 0:17am

binu.emiliya

Updated 7-Sep-16 18:18pm

Add a Solution

Comments

Maciej Los 7-Sep-16 11:14am

-- posted per mistake --

3 solutions

Solution 2

To be able to exclude some data, you need to use EXSISTS[^] or EXCEPT[^] method.

For example:

SQL

SELECT *
FROM @testawards
WHERE Details <> 'good'
EXCEPT
SELECT *
FROM @testawards 
WHERE Details = 'good'

Another way is to use JOIN's. See solution 1 by David_Wimbley[^]. For further details about how the JOINs work, see: Visual Representation of SQL Joins[^]

[EDIT]

binu.emiliya[^] wrote:
student 43 for term 2015 Spring there is no honor so i need to keep that record

Above reply to David_Wimbley[^]'s answer is bit confusing.
I checked that using pivot:

SQL

SELECT stid, AYear, [spring], [fall]
FROM (
	SELECT stid, AYear, Aterm, Details 
	FROM @testawards
	) AS DT
PIVOT (MAX(Details) FOR Aterm IN([spring], [fall])) AS PT
ORDER BY PT.stid, PT.Ayear

and my result is

stid	AYear	spring		fall
43		2014	NULL	
43		2015	good		Honor List
43		2016	Honor List	NULL
45		2014	NULL	
45		2015	good		PresidentList
45		2016	Honor List	NULL

So, you have to think about that.

Posted 7-Sep-16 8:09am

Maciej Los

Updated 7-Sep-16 20:19pm

v2

Comments

binu.emiliya 8-Sep-16 1:23am

Hi Maciej Los,

Thank You.I used Exists but did not get the result i was looking for
In your solution i can see it is excluded 2015 Spring .
If there is no honor and detail is good i need to keep that record
so there will be a record for each term.
Thanks for your support

Maciej Los 8-Sep-16 2:20am

See update answer.

binu.emiliya 8-Sep-16 8:50am

Thanks Maciej,

I used Mica's script and worked ! Thank you very much for your effort

Member 13598595 6-Mar-18 2:29am

nice

Member 13598595 6-Mar-18 2:29am

cvvvb

Solution 1

I think this provides what you are looking for. Also, thank you for providing schema and sample data, that made helping you so much easier.

SQL

    SELECT B.* FROM 
(SELECT 
    stid,  
    ayear, 
    aterm 
 FROM @testawards
 GROUP BY stid, ayear, aterm
 HAVING COUNT(*) > 1) AS A
 RIGHT JOIN @testawards AS B ON B.stid = A.stid AND b.Ayear = A.Ayear AND B.Aterm = A.Aterm
WHERE B.Details <> 'good'

Posted 7-Sep-16 6:56am

David_Wimbley

Updated 7-Sep-16 6:57am

v2

Comments

Maciej Los 7-Sep-16 14:10pm

Good work, David.

binu.emiliya 8-Sep-16 1:33am

Hi David,Thank you very much.I have to create the schema and sample data to make your work easier :)
This is very close to my result but it excludes rows which have detail as 'good'
For each term ,if i have any honor list i have to exclude 'good' record
For the first one ( Award and Detail as '' ) is another exception which we need to keep as such ( that term for that student is foundation so there is no honor or good)

these are the scenarios

1.award and detail nil or space ( not null) - keep it as same

2.award is nil or space detail is good- if there is other record in this term and year
exclude it else ( if there is no honor list in that term and semester) need to keep that record

for example student 43 for term 2015 Spring there is no honor so i need to keep that record with good as such.
But in Spring 2016 you can see two records one is honor and second is good.In this case i have to keep the honor. I hope my explanation is good

Thanks Again

Maciej Los 8-Sep-16 2:08am

Student 43 has been awarded twice: spring - 'good' and fall - 'Honor List'. I checked it using pivot:

stid	AYear	spring	fall
43	2014	NULL	
43	2015	good	Honor List
-^       ^       ^       ^
43	2016	Honor List	NULL
45	2014	NULL	
45	2015	good	PresidentList
45	2016	Honor List	NULL

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)

Wendelius · Accepted Answer · 2016-09-07T18:18:00

If I understand your requirement correctly, here's one variation you can try.

SQL

SELECT *
FROM  testawards ta
WHERE ta.Details = 'Good'
AND   NOT EXISTS ( SELECT 1
                   FROM testawards ta2
                   WHERE ta2.stid  = ta.stid
                   AND   ta2.Ayear = ta.Ayear
                   AND   ta2.Aterm = ta.Aterm
                   AND   ta2.Award = 'HONOR')

EDIT:
If records with something else than 'Good' need to be included, try varying as follows:

SQL

SELECT *
FROM  testawards ta
WHERE ta.Details <> 'Good'
OR   NOT EXISTS ( SELECT 1
                  FROM testawards ta2
                  WHERE ta2.stid  = ta.stid
                  AND   ta2.Ayear = ta.Ayear
                  AND   ta2.Aterm = ta.Aterm
                  AND   ta2.Award = 'HONOR')

Exclude records using a subquery in SQL SERVER

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us