CODEWITHSUNDEEP
mysqlsql
L Panthi Maniv
L Panthi Maniv

Reputation: 13

how to get two table joined data exclude some conditional data of one table in mysql?

I have two tables: Student and fee

student

sid name roll_no
1 John 22
2 Karina 32
3 Navin 42

fee

fid s_id month fee
1 2 January 1000
2 3 January 1200
3 2 Fabruary 1000

I want to get students (who not paid fee) for Fabruary : like this...

Student id Name Roll No January February
1 John 22 0 0
3 Navin 42 1200 0

My code is :

SELECT s.sid,s.name,s.roll_no,f.fee
from student s 
 left join
     fee f 
     ON f.fid = s.sid 
AND f.month = 'January' 
where s.sid NOT IN  (SELECT s_id from fee
                     where month = 'February') 
order by c.id;

I got zero value in both months for all students ------Thanks in advance-------

After correction this code works in mysql workbench but not in java application.

SELECT 

s.sid,s.name,s.roll_no, ifnull(f.fee,0) as pre_month,0 as current_month

from student s 

 left join

 fee f 

 ON f.s_id = s.sid 

 AND f.month = 'January' 

 where s.sid NOT IN  

   (SELECT s_id from fee

   where month = 'February') 

 order by s.sid;

Upvotes: 1

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

For the results you want, I would suggest conditional aggregation and a having clause:

SELECT s.sid, s.name, s.roll_no,
       SUM(CASE WHEN f.month = 'January' THEN f.fee ELSE 0 END) as january,
       SUM(CASE WHEN f.month = 'February' THEN f.fee ELSE 0 END) as february
FROM student s LEFT JOIN
     fee f 
     ON f.fid = s.sid AND f.month = 'January' 
GROUP BY s.sid, s.name, s.roll_no
HAVING SUM(CASE WHEN f.month = 'February' THEN f.fee ELSE 0 END) = 0 AND
       SUM(CASE WHEN f.month = 'January' THEN f.fee ELSE 0 END) > 0;

Here is a db<>fiddle.

Note that storing month names in a column is usually a really bad idea. It does not distinguish between the years, for instance. It is much better to use a date.

Upvotes: 0

Josh Pospisil
Josh Pospisil

Reputation: 215

You can use a left join to join the students with their fees, and then a having clause to filter the list down to those who don't have one for February. The below query should get you a list of all students who did not pay a fee for February.

It also looked like you might be using the wrong column on the fee table for the join. It looked like you might want s_id instead of fid.

select s.*
from students s
left join fee f on f.s_id=s.sid and f.month='February'
having f.fid is null;

Upvotes: 0

Related Questions