CODEWITHSUNDEEP
sqlms-access
SuppaiKamo
SuppaiKamo

Reputation: 285

Fastest way to SQL select records based on values of multiple related records in another table (one-to-many) in MS Access?

Given the displayed tables (simplified):

Tables

How do I in the fastest possible way select records in the main table where all related records in the "Related Table" are completed (=true) and the latest of the "CompletedTimestamp" values for each order is larger/smaller than a specified value?

If I wanted completed orders completed before 20/08/2013, a single record with the OrderId = 1 should be returned.

The database is MS Access, but I'm referencing it from another system using SQL and ADO.NET.

Upvotes: 0

Views: 323

Answers (1)

GarethD
GarethD

Reputation: 69789

The key is to use HAVING to limit you records rather than WHERE.

Assuming CompletedTimestamp is NULL where the order is not completed you can use this:

SELECT  m.OrderID, m.Owner, MAX(r.CompletedTimestamp) AS CompletedDate
FROM    [Main Table] m
        INNER JOIN [Related Table] r
            ON r.OrderID = m.OrderID
GROUP BY m.OrderID, m.Owner
HAVING  COUNT(r.CompletedTimeStamp) = COUNT(*)
AND     MAX(r.CompletedTimestamp) < '20130821';

Since COUNT(*) will count all records, and COUNT(CompletedTimestamp) will count non null CompletedTimestamps (because COUNT(NULL) = 0), this will only return orders where all related records are compelted.

Finally the statement MAX(CompletedTimestamp) < '20130821' will ensure only orders with a max completed date before the specified date are returned.

Upvotes: 1

Related Questions