CODEWITHSUNDEEP
mysql
Monicka Akilan
Monicka Akilan

Reputation: 1549

Excluded row based on a another table cell value in mysql

I have two tables.

student master enter image description here Feemaster enter image description here I executed the query 

SELECT f.standard,fee_type,fee_name,amount,ac_year FROM fee_master f, student_master s
where
s.standard=f.standard and
s.admission_no='21300';

I got resultset like enter image description here

But my need is if studnet_master.ac_year==AC16 then INITIAL FEE should get removed from resultset or if studnet_master.ac_year==!=AC16 then ADMISSION FEE should get removed from resultset

Can anyone please help me to sort out this....

Thanks in advance...

Upvotes: 1

Views: 35

Answers (2)

Dhaval Bhavsar
Dhaval Bhavsar

Reputation: 495

You can put condition in and statement.
Query:-
SELECT f.standard,fee_type,fee_name,amount,ac_year FROM fee_master f, student_master s
where
s.standard=f.standard and
s.admission_no='21300' and
IF(s.ac_year = 'AC16',FEE_NAME != 'INITIAL FEES',FEE_NAME != 'ADMISSION FEES')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Just add two extra conditions to your WHERE clause:

SELECT f.standard, f.fee_type, f.fee_name, f.amount, s.ac_year
FROM fee_master f
INNER JOIN
student_master s
    ON s.standard = f.standard
WHERE s.admission_no = '21300' AND
    (s.ac_year != 'AC16' OR f.fee_name != 'INITIAL FEES') AND
    (s.ac_year = 'AC16' OR f.fee_name != 'ADMISSION FEES')

Upvotes: 3

Related Questions