Corsair™ Collection Faucets

Looks that could soften the hardest water.

Introducing Corsair. With a gently curved handle and spout that draw their inspiration from windswept waves. This new collection includes a single handle lavatory faucet, three-function showerheads, a Roman tub faucet with personal sprayer and a matching soap/lotion dispenser.

SELECT prod.name as prodname, col.html_code as trade,col.name as ColName, subcol.name as SubColName,subcol.html_code as strade,col.description, tblcollection.tagname as coltag, pd.base_sku,colgroup.name as colgroupname ,tblgroup.tagname as grouptag,pd.sku , pd.listprice , pd.seq_order,

tblmidgroup.tagname as midgrouptag,colmid.name as midgroupname,tblmidgroup.midgroupID  from tblproduct pd  INNER JOIN tblproduct_midgroup ON  pd.productID = tblproduct_midgroup.productID

INNER JOIN tblmidgroup   ON tblmidgroup.midgroupID = tblproduct_midgroup.midgroupID

INNER JOIN tblgroup   ON  tblgroup.groupID = tblmidgroup.groupID

INNER JOIN tblcaption colmid ON colmid.captionID = tblmidgroup.midgroupID and colmid.type = 11 and colmid.groupID = 2

INNER JOIN tblcollection ON pd.collectionID = tblcollection.collectionID

LEFT JOIN tblcaption subcol ON pd.subcollectionID = subcol.captionID and subcol.type = 17 and subcol.languageID= 1

INNER JOIN tblcaption prod ON prod.captionID = pd.productID  AND prod.type=7 and prod.languageID= 1

INNER JOIN tblcaption col ON col.captionID = tblcollection.collectionID AND col.groupID = 2 AND col.type=2  and col.languageID=1

INNER JOIN tblcaption colgroup ON colgroup.captionID = tblgroup.groupID  AND colgroup.type=10  and colgroup.languageID= 1

WHERE pd.collectionID= 30 and pd.active=1 and pd.discontinued=0 and tblcollection.active=1 AND tblmidgroup.groupID= 2

and tblmidgroup.midgroupID = 5 group by base_sku

Product Search

Register/Login

Search Bathroom

Corsair™ Collection Faucets

Looks that could soften the hardest water.