SELECT viewRequests.Course, viewRequests.Priority, viewRequests.ReqDate, viewRequests.FirstName, viewRequests.LastName, viewRequests.Department, viewRequests.Status, Rates.Price, viewRequests.PriorityID FROM viewRequests LEFT JOIN Rates ON viewRequests.CourseID = Rates.CourseID AND viewRequests.DeliveryDate <= Rates.ExpiryDate And viewRequests.DeliveryDate >= Rates.EffectiveDate ORDER BY viewRequests.Course, viewRequests.LastName;
The tables it uses look like this: viewRequests Rates ------------ ----- ID ID ReqDate VendorID DeliveryDate CourseID Course EffectiveDate CourseID ExpiryDate Priority Price PriorityID FirstName LastName Department StatusThe problem is that there can be two or more sets of rates from different vendors that have the same date range and I want to select for each request the lowest possible price.