/* results.asp Keyword U= */ SELECT Markers.MarkerID, Result1.Rank, CAST (CASE WHEN Country='United States of America' THEN 1 ELSE 0 END AS bit) as USA, ReplacedByID, Markers.Title, Subtitle, Subtitle2, MarkerNo, WantList, Text, Latitude, Longitude, Intown, RoadName, RouteNo, Crossroads, RoadVisible, Milemarker, ROUTEType, RouteAlt, RouteName, NearestCrossRoad, CrossRoadRouteNo, CrossRoadDistance, Miles, CrossRoadDirection, CrossRoadRouteType, CrossRoadRouteAlt, CrossRoadRouteName, Location, DirectionOfTravel, ReportedMissing, ConfirmedMissing, MissingModifier, Country, State, Town, County, Neighborhood, Markers.CreatedBy, Markers.Accepted, Markers.Changed, Markers.Withdrawn, Markers.Rejected, Marker, Memorial, YearErected, Tag1, Tag2, Tag3, Tag4, Tag5 FROM (Markers INNER JOIN (SELECT MarkerID, SUM(Rank) AS Rank FROM (SELECT [Key] AS MarkerID, [RANK] * 100 / 100 AS Rank FROM CONTAINSTABLE(Markers,(Title, Subtitle, Subtitle2),'FORMSOF (INFLECTIONAL, "king and queen county") AND FORMSOF (INFLECTIONAL, virginia)') UNION ALL SELECT [Key], [RANK] * 40 / 100 FROM CONTAINSTABLE(Markers,Text,'FORMSOF (INFLECTIONAL, "king and queen county") AND FORMSOF (INFLECTIONAL, virginia)') UNION ALL SELECT [Key], [RANK] * 25 / 100 FROM CONTAINSTABLE(Markers,(Organization,Keywords),'FORMSOF (INFLECTIONAL, "king and queen county") AND FORMSOF (INFLECTIONAL, virginia)') UNION ALL SELECT [Key], [RANK] * 15 / 100 FROM CONTAINSTABLE(Markers,(Comments,SubjectComments),'FORMSOF (INFLECTIONAL, "king and queen county") AND FORMSOF (INFLECTIONAL, virginia)') UNION ALL SELECT Photos.MarkerID, MAX(Result2.[RANK]) * 50 / 100 FROM CONTAINSTABLE(Photos,*,'FORMSOF (INFLECTIONAL, "king and queen county") AND FORMSOF (INFLECTIONAL, virginia)') AS Result2 INNER JOIN Photos ON Photos.PhotoID=Result2.[Key] WHERE Photos.Accepted Is Not Null AND Photos.Withdrawn Is Null AND Photos.Rejected Is Null GROUP BY Photos.MarkerID UNION ALL SELECT Comments.MarkerID, MAX(Result2.[RANK]) * 50 / 100 FROM CONTAINSTABLE(Comments,*,'FORMSOF (INFLECTIONAL, "king and queen county") AND FORMSOF (INFLECTIONAL, virginia)') AS Result2 INNER JOIN Comments ON Comments.CommentID=Result2.[Key] WHERE Comments.Accepted Is Not Null AND Comments.Withdrawn Is Null AND Comments.Rejected Is Null AND Comments.MarkerID<>9 GROUP BY Comments.MarkerID UNION ALL SELECT Links.MarkerID, MAX(Result2.[RANK]) * 25 / 100 FROM CONTAINSTABLE(Links,*,'FORMSOF (INFLECTIONAL, "king and queen county") AND FORMSOF (INFLECTIONAL, virginia)') AS Result2 INNER JOIN Links ON Links.LinkID=Result2.[Key] WHERE Links.Accepted Is Not Null AND Links.Withdrawn Is Null AND Links.Rejected Is Null GROUP BY Links.MarkerID) AS Combined GROUP BY MarkerID) AS Result1 ON Markers.MarkerID=Result1.MarkerID) LEFT JOIN Tags ON Tags.MarkerID=Markers.MarkerID AND UserID=0 WHERE Markers.Accepted Is Not Null AND Markers.Withdrawn Is Null AND Markers.ReplacedByID Is Null AND Markers.Rejected Is Null ORDER BY Rank DESC, USA, Country, State, County, Town, Neighborhood, Markers.Title