gistfile1.sql

SELECT *
FROM (
  SELECT
		`VendorRequestOptions`.*,
		`VendorRequests`.*,
		(
			SELECT COUNT(DISTINCT `CountingRequests`.`Client`)
			FROM `VendorRequests` AS `CountingRequests`
			WHERE (
				`CountingRequests`.`Vendor`=$id AND
				IFNULL(`CountingRequests`.`When` > `LastUpdate`.`When`, true) AND
				`CountingRequests`.`Option`=`VendorRequests`.`Option`
			)
			
		) AS `Pending`
	FROM `VendorRequests`
	
	LEFT JOIN (
		SELECT *
		FROM (
			SELECT *
			FROM `MfgUpdates` AS `UpdatesOrdered`
			ORDER BY `UpdatesOrdered`.`When` DESC
		) `GroupingUpdates`
		GROUP BY `GroupingUpdates`.`Mfg`, `GroupingUpdates`.`Option`
	) `LastUpdate` ON (
		`VendorRequests`.`Vendor`=`LastUpdate`.`Mfg` AND
		`VendorRequests`.`Option`=`LastUpdate`.`Option`
	)
	
	JOIN `VendorRequestOptions` ON (
		`VendorRequests`.`Option`=`VendorRequestOptions`.`ID`
	)
	
	WHERE
		`VendorRequests`.`Vendor`=$id AND
		IFNULL(`VendorRequests`.`When` > `LastUpdate`.`When`, true)
) AS `UngroupedVendorRequests`
GROUP BY `UngroupedVendorRequests`.`Option`
ORDER BY `Pending` DESC