Need Help In Designing a query for returning students who did not pay fees

[BlueFox]

I need to list the names of student who did not pay the fees (there are mutiple fees types). The logic would be find those students who does not have the entry in the feespayment table for that particular fees type.

--Here are the table structures
'Student Table : studentid , name
Fees Table : Feesid,Feesname
Feespayment Table : studentid (from students table),feesid(from fees table),amount
--
So I need To Find those students who did not pay that fees (Remind, there are multiple fees ids), a.k.a. those students who are not in the feespayment table with the fees id. For Example , If student 1 did not pay the fees type 2, the he must be listed along with that fees name. If he has paid,no need to display him(with that fees )

Help Please

[jmcilhinney]

Are you going to specify one fee type at a time or do you want a list of all students who haven't paid a particular fee type for all fee types at the same time?

[BlueFox]

Are you going to specify one fee type at a time or do you want a list of all students who haven't paid a particular fee type for all fee types at the same time?

A list of all students who haven't paid any fees type. That is what I want. If a student has paid all the fees type, he should be not in the result. If other student has paid one, from total of four fee types, he must be listed in 3 rows along with those fees types he hasn't paid. It is called 'Fees Unpaid Report' or 'Fees Dues Report'
T.You.

[jmcilhinney]

I believe that something along these lines should work:

SELECT s.StudentId, s.Name, f.FeesId, f.FeesName
FROM FeesPayment fp RIGHT JOIN Student s
ON fp.StudentId = s.StudentId RIGHT JOIN Fees f
ON fp.FeesId = f.FeesId
WHERE fp.Amount IS NULL

[CVMichael]

This returns the student who did not pay any type of fee (or none). If all fees are paid, it won't be returned

SELECT s.*
    , CASE WHEN fp1.Feesid IS NULL THEN 'No' ELSE 'Yes' END AS [Fee 1 Paid]
    , CASE WHEN fp2.Feesid IS NULL THEN 'No' ELSE 'Yes' END AS [Fee 2 Paid]
    , CASE WHEN fp3.Feesid IS NULL THEN 'No' ELSE 'Yes' END AS [Fee 3 Paid]
    , CASE WHEN fp4.Feesid IS NULL THEN 'No' ELSE 'Yes' END AS [Fee 4 Paid]
FROM Student AS s
    LEFT JOIN Feespayment AS fp1 ON fp1.studentid = s.studentid AND fp1.Feesid = 1
    LEFT JOIN Feespayment AS fp2 ON fp2.studentid = s.studentid AND fp2.Feesid = 2
    LEFT JOIN Feespayment AS fp3 ON fp3.studentid = s.studentid AND fp3.Feesid = 3
    LEFT JOIN Feespayment AS fp4 ON fp4.studentid = s.studentid AND fp4.Feesid = 4
WHERE fp1.studentid IS NULL
    OR fp2.studentid IS NULL
    OR fp3.studentid IS NULL
    OR fp4.studentid IS NULL

You have to hard-code the IDs of the fees (in the left joins), and the fees names in the "AS", so for "[Fee 1 Paid]", put the actual fee name

PS. You did not mention for what database you need this, and the query might be different depending on the database type

If you need the query to be dinamic, let me know, as it is much more complicated, and it depends on the database you are using

[BlueFox]

I believe that something along these lines should work:

SELECT s.StudentId, s.Name, f.FeesId, f.FeesName
FROM FeesPayment fp RIGHT JOIN Student s
ON fp.StudentId = s.StudentId RIGHT JOIN Fees f
ON fp.FeesId = f.FeesId
WHERE fp.Amount IS NULL

This is the result for your query

[BlueFox]

This returns the student who did not pay any type of fee (or none). If all fees are paid, it won't be returned
[highlight=sql]
SELECT s.*
, ...........
PS. You did not mention for what database you need this, and the query might be different depending on the database type

If you need the query to be dinamic, let me know, as it is much more complicated, and it depends on the database you are using

Yes, It is sql server 2000 and I need feesids and names to be dynamic. I have also added an amount column. If we can get the total, We can use it for each students entry in feespayments table total. I have attached a image in the other reply.