jeudi 30 novembre 2017

Using IF statements in MySQL

So i have 2 tables of this format,

actual_data:

ID,ROLL_NO,ADM_DT,FEES
1,9895987650,2017-11-14,10000
2,78578686,2017-11-10,130
3,434343,2017-11-15,20

temp_data:

ID,R_NO,FS,FT
1,9895987650,9000,F1
5,9895987650,5000,F2
3,78578686,150,F3
4,123456,24141,F4

I'm trying to create a stored-procedure here,

DELIMITER $$    
CREATE PROCEDURE `myproc`()
BEGIN
SELECT a.*,b.* FROM actual_data a, temp_data b
    WHERE a.ROLL_NO = b.R_NO 
        AND 
        ((b.FS >= a.FEES AND b.FT IS NOT NULL) 
        OR 
        (b.FS < a.FEES AND b.FT IS NOT NULL)); 
END $$
DELIMITER;  

This will give me the following result:

ID,ROLL_NO,ADM_DT,FEES,ID,R_NO,FS,FT
1,9895987650,2017-11-14,10000,1,9895987650,9000,F1
1,9895987650,2017-11-14,10000,5,9895987650,5000,F2
2,78578686,2017-11-10,130,3,78578686,150,F3

Now this is where i'm getting stuck.

What i would like to do is,

IF b.FS >= a.FEES AND b.FT IS NOT NULL is TRUE, i would like to select R_NO,sum(FEES) GROUP BY R_NO HAVING sum(FEES) > 1000 ORDER BY R_NO from the above query result.

ELSE IF b.FS < a.FEES AND b.FT IS NOT NULL is TRUE, i would like to select R_NO,sum(FEES) GROUP BY R_NO HAVING sum(FEES) < 1000 ORDER BY R_NO from the above query result.

Is this possible to do through a stored-procedure? If so, could I get some suggestions as to how this can be done? I'm a newbie when it comes to MySql, so kindly help me out.

Aucun commentaire:

Enregistrer un commentaire