SQL: SELECT MAX(Date) involving a couple of tables

Question

I have a small MS Access database containing four tables and are struggeling with a MAX(Date) query which involves all tables. Any help will be greatly appreciated.

Overview of the database:

TABLE          COLUMNS

Person         Person_ID |....
Games          Game_ID | Name |....
Played_Games   Played_Games_ID | Game_ID | Date |....
Participation  Played_Games_ID | Person_ID |...

To list out all games and what date each of them were last played by any person I use:

SELECT DISTINCT Games.Name,
(SELECT MAX(Date) FROM Played_Games WHERE Played_Games.Game_ID = Games.Game_ID) AS Date_Last_Played
FROM Games
ORDER BY Games.Name

This works great, but I would also like to list what date each of them were last played by members of a certain group of persons. Any help with that will be greatly appreciated.

I asked a somewhat similar question some days ago, but then my goal was to list out all games, how many times each game is played in total and how many times each games is played by members of a certain group of persons (Persons 1 and 2 are just an example). GarethD helped me out with that one just perfect by the code below. I've tried to alter this code to solve my new task, cause I guess much of this logic/structure can be reused, but so far I've not been successful on my own :-(

SELECT  Games.Name,
        IIF(ISNULL(TimesPlayed),0,TimesPlayed) AS Times_Played,
        IIF(ISNULL(TimesPlayedByGroupMembers),0,TimesPlayedByGroupMembers) AS Times_Played_By_Group_Members
FROM    Games
        LEFT JOIN
        (   SELECT  Game_ID,
                    COUNT(*) AS TimesPlayed,
                    SUM(IIF(ISNULL(Participation.Played_Games_ID),0,1)) AS TimesPlayedByGroupMembers
            FROM    Played_Games
                    LEFT JOIN
                    (   SELECT  Played_games_ID
                        FROM    Participation
                        WHERE   Person_ID IN (1, 2)
                        GROUP BY Played_games_ID
                    ) AS Participation
                     ON Participation.Played_Games_ID = Played_Games.Played_Games_ID
            GROUP BY Game_ID
        ) AS Played_Games
            ON Played_Games.Game_ID = Games.Game_ID
ORDER BY Games.Name

Any help will be greatly appreciated. Thanks for your time!

"members of a certain group of persons" not sure what that means. do you just need to add a where clause so you get the players you want ? like where Person_ID in (...) ? — yael alfasi, Feb 23 '12 at 12:02
Thanks for your quick reply and sorry for my late response. `...AND Person_ID IN (...)` is in this case not enough, cause you have to find out, isolate and then operate only on the Played_Games_ID's that are connected to these spesific persons. I appreciate your respons and your will to help, but the problem is now solved by [GarethD](http://stackoverflow.com/users/1048425/garethd). Thanks — Morten, Feb 24 '12 at 11:24

score 1 · Accepted Answer · answered Feb 23 '12 at 12:04

I've just added in a couple of lines to the last answer I posted to show the date last played, and the date last played by group members, next to the number of times played. Hope this is what you are after.

SELECT  Games.Name,
        IIF(ISNULL(TimesPlayed),0,TimesPlayed) AS Times_Played,
        IIF(ISNULL(TimesPlayedByGroupMembers),0,TimesPlayedByGroupMembers) AS Times_Played_By_Group_Members,
        LastPlayed,
        LastPlayedByGroupMembers
FROM    Games
        LEFT JOIN
        (   SELECT  Game_ID,
                    COUNT(*) AS TimesPlayed,
                    SUM(IIF(ISNULL(Participation.Played_Games_ID),0,1)) AS TimesPlayedByGroupMembers,
                    MAX(Played_Games.[Date]) AS LastPlayed,
                    MAX(IIF(ISNULL(Participation.Played_Games_ID),NULL,Played_Games.[Date])) AS LastPlayedByGroupMembers
            FROM    Played_Games
                    LEFT JOIN
                    (   SELECT  Played_games_ID
                        FROM    Participation
                        WHERE   Person_ID IN (1, 2)
                        GROUP BY Played_games_ID
                    ) AS Participation
                     ON Participation.Played_Games_ID = Played_Games.Played_Games_ID
            GROUP BY Game_ID
        ) AS Played_Games
            ON Played_Games.Game_ID = Games.Game_ID
ORDER BY Games.Name

Thanks for your quick reply and sorry for my late response. Your new addition solved my problem just perfect. Thanks again for your help GarethD — Morten, Feb 24 '12 at 10:53
Could you tell me why you put brackets around Date by the way? The query seems to work both with og without them, so I'm just curious what the brackets are for (in general) — Morten, Feb 24 '12 at 10:55
They are used to disambiguate between SQL Key words and object names, or when object names have a space in (Usually avoidable with good design). e.g. If you had a table with a column called "Select" and wrote the following Query `SELECT select FROM Table` you'd get an incorrect syntax error, however using `SELECT [select] FROM Table` The query then knows that [select] refers to the column "select" rather than the SQL key word `SELECT`. - http://stackoverflow.com/questions/153861/brackets-in-sql-statements — GarethD, Feb 24 '12 at 11:07

SQL: SELECT MAX(Date) involving a couple of tables

1 Answers1

Linked