CODEWITHSUNDEEP
sqlsql-server
Babai
Babai

Reputation: 97

How to get this output using JOIN

I have a table where I am storing all student records who belongs to a particular class.

Class_ID|StudentID|Class_FEE
----------------------------
1001    |  0      |   100   
1001    |  101    |   50

Now another table stored student data while they registered on particular class

ClassID | StudentID
--------|-----------
1001    |   100
1001    |   101

Now on first table 0 indicates all students (here 100,101) . Now both have fees 100 but 101 got 50 for some purpose.

I need a o/p like 

ClassID |  StudentID  | Amount
--------|-------------|--------
1001    |   100       |100
1001    |   101       |  50 (here 50 <100 so taking 50)

Upvotes: 0

Views: 77

Answers (3)

Zorkolot
Zorkolot

Reputation: 2027

You could just do:

DECLARE @particularclass TABLE (Class_ID int, StudentID int, Class_FEE int)
DECLARE @regclass TABLE (ClassID int, StudentID int)
INSERT INTO @particularclass VALUES (1001,0,100), (1001,101,50)
INSERT INTO @regclass VALUES (1001,100), (1001,101)

SELECT R.*
      ,COALESCE((SELECT MIN(Class_FEE) FROM @particularclass P WHERE P.StudentID = R.StudentID)
               ,(SELECT CLass_FEE FROM @particularclass P WHERE P.StudentID = 0)) AS [Amount]
  FROM @regclass R

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270503

You can do:

select sc.studentid,
       (case when f.fee < f0.fee then f.fee else f0.fee end) as fee
from studentclass sc left join
     fees f
     on sc.studentid = f.studentid and sc.classid = f.classid left join
     fees f0
     on sc.classid = f0.classid and f0.studentid = 0;

This version assumes (as in your sample data) that the a student/class combination only appears once in the fee table.

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10711

If you are interested about the minimum fee belonging to each student you can use this

select r.classid , r.studentid, min(b.Class_FEE)
from registered r
join belongs b on b.classid = r.classid and 
                 (b.studentid = r.studentid or 0 = b.studentid)
group by r.classid , r.studentid

demo

Result

ClassID StudentID   Amount
-------------------------------------
1001    100         100
1001    101         50

Upvotes: 0

Related Questions