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

7 Stalie Court
Staten Island, NY 10309

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,288,000
Beds: --
Baths: 4
Status: Active
SqFt: 2380
Agency: J. Milo Real Estate Ltd

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: --
Property Type: Residential
Year Built: 2011
Notes: Welcome to your new home! Nestled in the serene neighborhood of Pleasant Plains on the South Shore of Staten Island. This Fully detached 2-family Center hall colonial exudes charm and comfort. As you step inside, you'll be greeted by a grand 2-story foyer leading to elegant formal living and dining rooms boasting 9-foot ceilings and exquisite crown molding. The heart of the home is the spacious eat-in kitchen featuring granite countertops, stainless steel appliances, classic cherry wood cabinets, and charming wainscoting throughout. The cozy family room is perfect for gatherings, complete with a gas fireplace and gleaming hardwood floors. Step outside to your private oasis - a stunning backyard retreat featuring a gazebo, outdoor Custom kitchen w/ Commercial Bbq , refrigeration , Granite Counters , outdoor Sink offers Hot water for convenience and Also Storage. Custom beautiful pavers, ideal for entertaining or enjoying quiet moments. Upstairs, discover 4 generously sized bedrooms including your luxurious master suite, offering ample storage and comfort. Own parking spot and side entrance provides flexibility and convenience. With a one-car garage, carport, and abundant private parking, you'll never have to worry about space for you and your guests. Plus, enjoy peace of mind with security cameras and an alarm system already in place. Situated in a tranquil development of only 7 homes, this property offers a rare opportunity for privacy and tranquility. Don't miss out on the chance to make this exceptional home yours - schedule your private showing today!
MlsNumber: 481604


Listing Provided By: J. Milo Real Estate Ltd, original listing
Name: J. Milo Real Estate Ltd
Phone: (718) 872-9300
Office Name: J. Milo Real Estate Ltd
Office Phone: (718) 872-9300
Agent Name: Jenine Milo
Disclaimer: Copyright © 2024 Brooklyn New York Multiple Listing Service. 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