Title Page Background
HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 207 AND i.igate IN ('usahud','mnhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mnhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 207 AND i.igate IN ('usahud','mnhud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mnhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 207 AND i.igate IN ('usahud','mnhud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mnhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;

9695 Heatherhearst Drive
Chowchilla, CA 93610

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $439,999
Beds: 3
Baths: 2
Status: Pending
SqFt: 2002
Agency: Sebastian Natalia Realtors

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: --
Property Type: Residential
Year Built: 2005
Notes: Welcome to 9695 Heatherhearst Drive, don't forget to bring your golf clubs! This delightful built home is packed full of beautiful finishes and upgrades. The open concept kitchen is the heart of the home, the Chef is not included in the sale. The well thought out design centered around the large topped island is perfect for preparing and enjoying family meals as well as entertaining. Stainless steel appliances, custom oak cabinets add to the overall beauty and convenience of the kitchen. The large open living room offers a gas fireplace & is ideal for family time and hosting gatherings in comfort. The home offers ample sized primary bedroom for the King and Queen of the house with an oversized walk in closet and a bathroom with plenty of elbow room! The two additional bedrooms offer plenty of space for guests or for the kids, there is an additional room that can be used as an additional bedroom, a study, office or a game room for poker night. Bring your compass because the backyard is huge, there is plenty of shade for those hot summer days & if you're into gardening this is your future home! The backyard has the following fruit trees and grape vine: Fruit trees: Apricot 10 yrs ,Blood Plum 7 yrs, Orange 10 yrs, Tangerine 10 yrs, White nectarine 2 yrs, Doughnut peach 8 yrs, Blackberrie 2 yrs, Peach 3 yrs, Nectarine 10 yrs Grapes: Thompson 6 yrs, Red flame 10 yrs, Concord 2 yrs With a huge variety of roses and flowers in continuous bloom in both front and back yard.
MlsNumber: 612020


Listing Provided By: Sebastian Natalia Realtors, original listing
Name: Sebastian Natalia Realtors
Phone: (559) 223-0563
Office Name: Sebastian Natalia Realtors
Office Phone: (559) 223-0563
Agent Name: Eddie Villarreal
Disclaimer: Copyright © 2024 Fresno Association of Realtors. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App