Get difference between two sql server rows

Question

0.00/5 (No votes)

See more:

Salutation to you all.

I have my sq l table in the example format below:

id      Stud          Amount    Sem

2       007           5600      SemI
3       002           4500      Sem1 
4       001           5600      SemI
5       007           4500      Sem1I
6       001           5600      SemII
7       002           4500      Sem1
8       002           4500      Sem1I
9       005           4500      SemII

How can i write an Sql query to get only those students who have made payments in semII only and NOT in semI.
E.g. Student 005

Thanks for your support

[edit]Code block added - OriginalGriff[/edit]

Posted 16-Feb-14 3:39am

katumbamartin

Updated 16-Feb-14 3:44am

OriginalGriff

v2

Add a Solution

5 solutions

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)

Peter Leow · Answer 1 · 2014-02-16T04:06:00

Solution 3

SQL

SELECT t1.stud FROM table1 t1 WHERE  t1.sem='semII' AND  NOT EXISTS
(SELECT * FROM table1 t2 WHERE t2.sem='semI' AND t1.stud=t2.stud)

Posted 16-Feb-14 4:06am

Peter Leow

Updated 16-Feb-14 4:19am

v2

OriginalGriff · Answer 2 · 2014-02-16T04:20:00

Solution 4

Try:

SQL

SELECT Stud FROM MyTable a
JOIN (SELECT stud, COUNT(sem) AS sems
      FROM MyTable
      WHERE Sem='SemI' OR Sem='SemII'
      GROUP BY Stud) b
ON a.Stud = b.Stud
WHERE b.sems=1 AND a.Sem='SEMII'

Posted 16-Feb-14 4:20am

OriginalGriff

joginder-banger · Answer 3 · 2014-02-16T04:04:00

Solution 2

hi you can do it...with the help of where condition...

Where Condition is used like that a bool condition. for example is

SQL

select count(*) from TableName where columnname="Condition Value"

if the above query is return 1 then where condition is true other wise condition is false.
try this

SQL

Select * from Student where sem='SemII'

for any query hit to reply..

Posted 16-Feb-14 4:04am

joginder-banger

Comments

OriginalGriff 16-Feb-14 10:41am

Reason for my vote of one: Please read the question carefully before posting a solution.
The OP wants details based on data from two rows, which a simple WHERE condition can't do: it only looks at each row in isolation.

JatinKhimani · Answer 4 · 2014-02-16T23:49:00

Solution 5

SQL

SELECT * FROM table1 WHERE  sem='semII' AND sem NOT IN
(SELECT sem FROM table1 WHERE sem='semI')

Posted 16-Feb-14 23:49pm

JatinKhimani

Updated 17-Feb-14 0:54am

v2

Ainy Mughal · Answer 5 · 2014-02-17T01:24:00

Solution 6

SQL

SELECT [current].rowInt, [current].Value, ISNULL([next].Value, 0) - [current].Value
FROM sourceTable AS [current]
LEFT JOIN sourceTable AS [next]
      ON [next].rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > [current].rowInt)

Posted 17-Feb-14 1:24am

Ainy Mughal