S: SELECT TOP 50 M.MediaID, M.MediaHeading, M.MediaDesc, M.MediaDatePosted, M.MediaOrgType, M.MediaOrgID, CASE WHEN (M.MediaCityActive='Y' AND CO.COCityID IS NOT NULL) THEN 1 WHEN (M.MediaStateActive='Y' AND SO.SOStateID IS NOT NULL) THEN 2 WHEN (M.MediaRegionActive='Y' AND RO.RORegionID IS NOT NULL) THEN 3 WHEN (M.MediaNationActive='Y' AND NAO.NONationID IS NOT NULL) THEN 4 ELSE 99 END AS Priority, CASE WHEN M.MediaOrgType='E' THEN ENT.EntityName WHEN M.MediaOrgType='C' THEN C.CityDesc WHEN M.MediaOrgType='T' THEN T.CountyDesc WHEN M.MediaOrgType='S' THEN S.StateDesc WHEN M.MediaOrgType='R' THEN R.RegionDesc WHEN M.MediaOrgType='N' THEN NAT.NationDesc WHEN M.MediaOrgType='H' THEN CBA.ClubName WHEN M.MediaOrgType='B' THEN BS.BoardName END AS OrgName FROM Medias M LEFT JOIN Entitys ENT ON M.MediaOrgType='E' AND M.MediaOrgID=ENT.EntityID LEFT JOIN Citys C ON M.MediaOrgType='C' AND M.MediaOrgID=C.CityID LEFT JOIN Countys T ON M.MediaOrgType='T' AND M.MediaOrgID=T.CountyID LEFT JOIN States S ON M.MediaOrgType='S' AND M.MediaOrgID=S.StateID LEFT JOIN Regions R ON M.MediaOrgType='R' AND M.MediaOrgID=R.RegionID LEFT JOIN Nations NAT ON M.MediaOrgType='N' AND M.MediaOrgID=NAT.NationID LEFT JOIN Clubs CBA ON M.MediaOrgType='H' AND M.MediaOrgID=CBA.ClubID LEFT JOIN Boards BS ON M.MediaOrgType='B' AND M.MediaOrgID=BS.BoardID LEFT JOIN CityOrgs CO ON CO.COOrgType=M.MediaOrgType AND CO.COOrgID=M.MediaOrgID AND CO.COCityID=4686 LEFT JOIN StateOrgs SO ON SO.SOOrgType=M.MediaOrgType AND SO.SOOrgID=M.MediaOrgID AND SO.SOStateID=15 LEFT JOIN RegionOrgs RO ON RO.ROOrgType=M.MediaOrgType AND RO.ROOrgID=M.MediaOrgID AND RO.RORegionID=8 LEFT JOIN NationOrgs NAO ON NAO.NOOrgType=M.MediaOrgType AND NAO.NOOrgID=M.MediaOrgID AND NAO.NONationID=3 WHERE M.MediaActive='Y' AND M.MediaDisplayActive='Y' AND DATEDIFF(day, M.MediaDatePosted, GETDATE()) <= 30 AND ( (M.MediaCityActive='Y' AND CO.COCityID IS NOT NULL) OR (M.MediaStateActive='Y' AND SO.SOStateID IS NOT NULL) OR (M.MediaRegionActive='Y' AND RO.RORegionID IS NOT NULL) OR (M.MediaNationActive='Y' AND NAO.NONationID IS NOT NULL) ) AND MediaHouseActive = 'Y' ORDER BY Priority ASC, M.MediaID DESC