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 = 241 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 = 241 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 = 241 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;

48250 Fork Drive S
Three Rivers, CA 93271
James Cappello with Redfin Corporation, original listing - (877) 973-3346

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $5,199,900
Beds: 3
Baths: Full: 3
Status: Active
SqFt: 3550 Square Feet
Agency: Redfin Corporation
Agency Phone: (877) 973-3346

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 43 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2000
Notes: Nestled on over 43 sprawling acres just outside the Sequoia National Park, this extraordinary estate offers an unrivaled experience of nature's beauty combined with luxurious living. At its heart lies a majestic 22-foot waterfall that cascades into your own swimming ponds, creating a tranquil ambiance that soothes the soul.The North-facing main home spans over 3,500 sq. ft. & boasts a breathtaking great room with floor-to-ceiling windows & panoramic views of the waterfall, river & mountains. Vaulted wood ceilings, distressed oak flooring & a striking natural stone fireplace provide an opulent yet cozy retreat. Step up to the chef's kitchen with top-of-the-line JennAir appliances & an expansive granite island -perfect for entertaining.The owner's suites on both levels offer unparalleled luxury with Rainforest marble finishes, jacuzzi tubs & private access to the deck showcasing the stunning views. Step outside to the massive panoramic deck that wraps around the property and marvel at the views of Mt. Dennison & your own private waterfall. The estate's structural integrity is fortified with steel beams & footings, ensuring lasting durability, while the 400-foot well and owned solar power provide reliability & sustainability. The property also includes a 2 bedroom cedar guest house with river access. This magnificent property is a sanctuary of serenity & sophistication -a rare opportunity to own a slice of paradise with unparalleled natural wonders & world-class amenities.
MlsNumber: --
ListingId: 610546


Listing Provided By: Redfin Corporation, original listing
Phone: 973-3346
Office Phone: (877) 973-3346
Agent Name: James Cappello
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