SELECT s.fldProductID,s.fldProductCode, s.fldProvider FROM defaqto_stand_data AS s
JOIN mole_remortgage_products AS m ON m.fldProductID = s.fldProductID
-- loan amount is 200000
-- purchase price is 500000
WHERE '200000' < fldMaxLoan
AND (200000/500000)*100 BETWEEN fldMinimumLTV AND fldMaxLTVRemortgage
-- Term is 25 years
AND 25 BETWEEN fldMinTerm AND fldMaxTerm
AND fldTypeRepayment = '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'
UNION
SELECT d.fldProductID,d.fldProductCode,d.fldProvider FROM defaqto_disc_data AS d
JOIN mole_remortgage_products AS m ON m.fldProductID = d.fldProductID
-- loan amount is 200000
-- purchase price is 500000
WHERE '200000' < fldMaxLoan
AND (200000/500000)*100 BETWEEN fldMinimumLTV AND fldMaxLTVRemortgage
-- Term is 25 years
AND 25 BETWEEN fldMinTerm AND fldMaxTerm
-- rem property type and tenure needs to come from defaqto_lending_criteria
-- rem work out the age from the date fields
AND fldSelfEmployed = 'Yes'
UNION
SELECT f.fldProductID,f.fldProductCode,f.fldProvider FROM defaqto_fixed_data AS f
JOIN mole_remortgage_products AS m ON m.fldProductID = f.fldProductID
-- loan amount is 200000
-- purchase price is 500000
WHERE '200000' < fldMaxLoan
AND (200000/500000)*100 BETWEEN fldMinimumLTV AND fldMaxLTVRemortgage
-- Term is 25 years
AND 25 BETWEEN fldMinTerm AND fldMaxTerm
AND fldTypeRepayment = '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'
;