SELECT d.fldProductID,d.fldProvider, IF(GREATEST(fldMultiplier1st,fldMultiplier2nd,fldMultiplierJoint) IS NOT NULL,
GREATEST(fldMultiplier1st,fldMultiplier2nd,fldMultiplierJoint),
GREATEST(fldIncomeMultiples1st,fldIncomeMultiple2nd,fldIncomeMultiplesJoint)) AS maxMultiplier FROM defaqto_disc_data AS d,defaqto_lending_crit AS dlc
JOIN mole_remortgage_products AS m ON m.fldProductID = d.fldProductID
JOIN defaqto_multiplier_overrides dmo on dlc.fldProvider=dmo.fldProvider
-- loan amount is 200000
-- purchase price is 500000
WHERE '200000' < fldMaxLoan
AND dlc.fldProvider = d.fldProvider
AND (200000/500000)*100 BETWEEN fldMinimumLTV AND fldMaxLTVRemortgage
-- Term is 25 years
AND 25 BETWEEN fldMinTerm AND fldMaxTerm
AND fldTypeRepayment = 'Yes'
AND fldAvailableInWales = 'Yes'
-- rem property type and tenure needs to come from defaqto_lending_criteria
-- rem work out the age from the date fields
AND fldSelfEmployed = 'Yes'
AND fldSelfCertAvailable = 'Yes'
;