SELECT DISTINCT COALESCE(dc.Rank,0) + COALESCE(cn.Rank,0) + COALESCE(n.Rank,0) + COALESCE(a.Rank,0) + COALESCE(c.Rank,0) + COALESCE(t1.Rank,0) as Rank ,d.DirId, d.CompanyNameNoHTMLTag, d.Address1, d.Address2, d.City, d.State, d.Zip, d.Telephone1, d.Latitude, d.Longitude, d.gMapAccuracy FROM DirEntries as d INNER JOIN DirEntry2BookRegion as r ON d.DirId = r.DirId LEFT JOIN CONTAINSTABLE (DirEntries,DirCatName, ' FORMSOF(INFLECTIONAL, skate) ') AS dc ON d.DirId = dc.[KEY] LEFT JOIN CONTAINSTABLE (DirEntries,CompanyNameNoHTMLTag, ' FORMSOF(INFLECTIONAL, skate) ') AS cn ON d.DirId = cn.[KEY] LEFT JOIN CONTAINSTABLE (DirEntries,Note, ' FORMSOF(INFLECTIONAL, skate) ') AS n ON d.DirId = n.[KEY] LEFT JOIN CONTAINSTABLE (DirEntries,Address1, ' FORMSOF(INFLECTIONAL, skate) ') AS a ON d.DirId = a.[KEY] LEFT JOIN CONTAINSTABLE (DirEntries,City, ' FORMSOF(INFLECTIONAL, skate) ') AS c ON d.DirId = c.[KEY] LEFT JOIN CONTAINSTABLE (DirEntries,Telephone1, ' FORMSOF(INFLECTIONAL, skate) ') AS t1 ON d.DirId = t1.[KEY] WHERE COALESCE(dc.Rank,0) + COALESCE(cn.Rank,0) + COALESCE(n.Rank,0) + COALESCE(a.Rank,0) + COALESCE(c.Rank,0) + COALESCE(t1.Rank,0) > 0 AND d.CompanyNameNoHTMLTag IS NOT NULL AND r.BookRegionId in (0,1) AND (GetDate() >= DirStartDate or DirStartDate IS NULL) AND (GetDate() <= DirEndDate or DirEndDate IS NULL)ORDER BY rank desc