query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

angel9
Starting Member

10 Posts

Posted - 2009-04-06 : 16:57:01

Could someone give me some advice on how to remove ALL student records if that student has any credit which is less than 5. Thank you very much in advance.

The original table looks like this:
ID CREDIT
1 2
2 6
2 7
3 8
3 9
3 10
4 11
4 3

Result will look like this (Student ID 1 and 4 are removed from the original table because each student has a credit less than 5):
ID CREDIT
2 7
2 8
3 9
3 10
3 11

sriv
Starting Member

11 Posts

Posted - 2009-04-06 : 17:03:21

Select
*
from table
where ID Not in
(Select Id from table where credit < 5)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-06 : 17:16:03

[code]
Delete a
from
MyTable a
where exists (Select * from MyTable aa where aa.ID = aa.ID and aa.Credit <5)
[/code]

or try

[code]
Delete a
from
MyTable a
Inner join
MyTable b
on a.ID = b.ID
where b.Credit <5
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

angel9
Starting Member

10 Posts

Posted - 2009-04-06 : 17:18:07

thank you for your quick response to my posting. I have tested the query you provided; unfortunately. it works only for students with a single record (such as id = 1); however, it does not remove student records where id = 4 because it has more than one record; and one of them satisfies the requirement - credit > 5.

quote:
Originally posted by sriv

Select
*
from table
where ID Not in
(Select Id from table where credit < 5)

angel9
Starting Member

10 Posts

Posted - 2009-04-06 : 17:26:31

I forget mentioning that i can only query (using select statement only) and i am not allowed to delete/update tables at all.

sriv
Starting Member

11 Posts

Posted - 2009-04-06 : 17:40:44

-- Create table
Create table #table (ID int, credit int)
-- Insert Data
insert into #table values(1, 2)
insert into #table values(2, 6)
insert into #table values(2, 7)
insert into #table values(3, 8)
insert into #table values(3, 9)
insert into #table values(3, 10)
insert into #table values(4, 11)
insert into #table values(4, 3)

-- Select Statement
Select
*
from #table
where ID Not in
(Select Id from #table where credit < 5)

--Result Set

ID credit
----------- -----------
2 6
2 7
3 8
3 9
3 10

angel9
Starting Member

10 Posts

Posted - 2009-04-06 : 17:49:13

I got it. Thank you very much. I am not sure why i got a different result when I ran the query in the first place. Thanks again.

quote:
Originally posted by sriv

-- Create table
Create table #table (ID int, credit int)
-- Insert Data
insert into #table values(1, 2)
insert into #table values(2, 6)
insert into #table values(2, 7)
insert into #table values(3, 8)
insert into #table values(3, 9)
insert into #table values(3, 10)
insert into #table values(4, 11)
insert into #table values(4, 3)

-- Select Statement
Select
*
from #table
where ID Not in
(Select Id from #table where credit < 5)

--Result Set

ID credit
----------- -----------
2 6
2 7
3 8
3 9
3 10

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-06 : 17:53:07

Duplicate topic, locking this one.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources