mysql condition to validate data based on certain condtion

Question

I have two tables,namely booking and ledger.

booking table:

BookingID -- RegularPrice -- DownPayment  -- PerMonthInstallment
   1            100,000         10,000          7,500

ledger table:

BookingID  Voucher  -- PaymentType  -- Amount
1             1        Down Payment    10,000
1             2        Installment     7,500

Current output is;

PaymentType --  PerMonthInstallment -- PaidAmount
Down Payment        7,500                 7,500
Installment#1       7,500                 7,500

What i want is, if PaymentType is Down Payment then in the PerMonthInstallment column, the value should be 10,000 rather than 7,500.

PaymentType --    PermonthInstallment --    PaidAmount
Down Payment        10,000                   10,000
Installment#1       7,500                     7,500

My Query:

SELECT booking.BookingID,  
       booking.PerMonthInstallment, 
       ledger.PaymentType,
       ledger.PaidAmount,
       ledger.Voucher
from booking
INNER JOIN ledger ON booking.BookingID = ledger.BookingID

That's confusing. In your description you talk about a column `DueAmount` yet such a column is neither in your samples nor in your query. And instead of describing the logic you use values (7500, 10000), which leaves further room for (mis)interpretation. You should elaborate. — sticky bit, Dec 27 '19 at 05:15

score 0 · Answer 1 · answered Dec 27 '19 at 05:12

oK now, my first suggestion is you shouldn't use payment type using words. what you can do is you can give a number as an example let's say downpayment =1 installment =2 so when you're using them you can put condition from them for your query question you can use IF

SELECT IF(PaymentType=='Down Payment',10000,7500) AS Dueamount FROM TableName;

if you're changing to my suggestion

   SELECT IF(PaymentType==1,10000,7500) AS Dueamount FROM TableName;

the reason is let's say there is going to be extra space in somewhere but you can't see it still program considering those spaces it will take some time to notice that space so use number instead of this and this is database anomaly

mysql condition to validate data based on certain condtion

1 Answers1