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'
;