Multi-Function Showerheads

No one offers a wider variety of ingenious showerhead features. Please choose your favorite showerhead for further detail.

SELECT  prod.name as prodname,tblsubgroup.tagname as subrouptag, col.name as ColName, subcol.name as SubColName,  col.description,pd.sku,pd.base_sku, colgroup.name as colgroupname ,grp.tagname as grouptag   ,md.tagname as midgrouptag,colmid.name as midgroupname,  collectcaption.html_code as trade , collectcaption.name as collectName,collectcaption.captionID as collectionID from tblproduct pd

INNER JOIN tblproduct_midgroup gp ON pd.productID = gp.productID

INNER JOIN tblmidgroup md ON md.midgroupID = gp.midgroupID

INNER JOIN tblcaption collectcaption ON collectcaption.captionID = pd.collectionID AND collectcaption.type=2 and collectcaption.languageID= 1

INNER JOIN tblgroup grp ON  grp.groupID = md.groupID

INNER JOIN tblcaption colmid ON colmid.captionID = md.midgroupID AND colmid.type=11  and colmid.groupID= 2 and colmid.languageID=1

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

INNER JOIN tblsubgroup ON pd.subgroupID = tblsubgroup.subgroupID

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

INNER JOIN tblcaption col ON col.captionID = tblsubgroup.subgroupID AND col.groupID = 2 AND col.type=12  and col.languageID=1

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

WHERE pd.subgroupID= 15

AND md.groupID= 2

AND gp.midgroupID= 2

AND pd.active=1 and pd.discontinued=0

GROUP BY base_sku order by pd.sku

Product Search

Register/Login

Search Bathroom

Multi-Function Showerheads