SQL query with GROUP BY to return concatenated list of on FIELD-CodePudding

I am using ADODB for excel to pull a list of undo entries from my undo table (Access database). The user will then select one of the "Undo:s" as defined by the unique combination of _MODIFIED_BY and _MODIFIED_TIME and then be able to retrieve all records related to that. A convenient way is to retrieve that by a list of ID:s.

So for the first Query I wish to create a list of all related ID:s (named _ORIG_ID:s) like this:

SELECT Count(*), [_MODIFIED_BY], [_MODIFIED_TIME], CONCAT([_ORIG_ID],',') AS _REL_IDS
FROM ANALYSES_UNDO
GROUP BY [_MODIFIED_BY], [_MODIFIED_TIME]
ORDER BY [_MODIFIED_TIME] DESC

Then the user will select one of the records from this query and I will then pull these ID:s from another table like this:

SELECT * FROM ANALYSES WHERE ID IN(XXX)

Where I will just replace the XXX with whatever was returned in _REL_IDS.

Is it possible to construct this query. Can ACCESS SQL even join a field like this? I know there is no function called CONCAT that works like this, but it was just defined to demonstrate my needs.

CodePudding user response：

You don't need to concatenate the data, it would be simpler to use the original query as a subquery in the second part.

To Demonstrate:
Original query (without the concat bit)

SELECT Count(*), [_MODIFIED_BY], [_MODIFIED_TIME], FIRST([_ORIG_ID])
FROM ANALYSES_UNDO
GROUP BY [_MODIFIED_BY], [_MODIFIED_TIME]
ORDER BY [_MODIFIED_TIME] DESC

Note the FIRST([_ORIG_ID]) part - it's returning one of the correct IDs, the second query can just compare the fields of that, as below:

SELECT * 
FROM ANALYSES 
INNER JOIN ANALYSES_UNDO ON ANALYSES.ID = ANALYSES_UNDO.[_ORIG_ID]
WHERE 
    ANALYSES_UNDO.[_MODIFIED_BY] = (SELECT U.[_MODIFIED_BY] FROM ANALYSES_UNDO U WHERE U.[_ORIG_ID] = XXX)
AND ANALYSES_UNDO.[_MODIFIED_TIME] = (SELECT U.[_MODIFIED_TIME] FROM ANALYSES_UNDO U WHERE U.[_ORIG_ID] = XXX)

The parameter XXX passed into this second query only needs to be a single [_ORIG_ID] and the query will return all other records with the same MODIFIED_TIME and MODIFIED_BY

CodePudding user response：

I finally found a way that worked for me by using the returned [_MODIFIED_BY]=XXX and [_MODIFIED_TIME]=YYY by the query below:

SELECT * FROM ANALYSES WHERE ID=
ANY(SELECT [_ORIG_ID] FROM ANALYSES_UNDO WHERE [_MODIFIED_BY]=XXX AND [_MODIFIED_TIME]=YYY)