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','cahud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'cahud', 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','cahud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'cahud', 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','cahud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'cahud', 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;

51951 Piute Road
North Fork, CA 93643
Kari Hayes with London Properties-Clovis, original listing - (559) 436-4000

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $949,900
Beds: 4
Baths: Full: 3
Status: Active
SqFt: 2550 Square Feet
Agency: London Properties-Clovis
Agency Phone: (559) 322-4000

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 89 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2020
Notes: Stunning 89.8-acre property with spectacular views and endless possibilities! Welcome to this extraordinary estate featuring a main home & an attached Additional Dwelling Unit for extended family or in-laws. The larger home consists of 2 bedrooms, 2 bathrooms and a loft overlooking the living area, boasting soaring ceilings with exquisite exposed wood beams. The luxurious primary bathroom offers a large jetted tub with a built-in heater & spacious walk-in shower. The kitchen is designed for a professional chef with a top-of-the-line gas range and large center island with sitting room all around. Nestled on nearly 90 acres of pristine land, this home offers 360-degree panoramic views capturing breathtaking sunsets & city lights. Both homes are equipped with tankless water heaters, split AC/Heater units in every room and cozy pellet stoves for year-round comfort. The kitchens are beautifully designed with granite countertops in addition to gorgeous, lifetime warranty vinyl flooring on both main levels adding warmth and elegance.Enjoy ultimate privacy with a gated entrance leading to the property & and the added dependability of a 900-foot well that pumps 50 gallons of water per minute! The land is ideal for various uses whether you're looking to raise horses or farm animals, host events, weddings, or explore hunting opportunities. You will appreciate sightings of bear, deer, and other local animals. More features too numerous to mention. Schedule your private tour today!
MlsNumber: --
ListingId: 618454


Listing Provided By: London Properties-Clovis, original listing
Phone: 605-9707
Office Phone: (559) 436-4000
Agent Name: Kari Hayes
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