Table alias not found in GROUP CONCAT sub-query used to narrows down concat results

Question

I am trying to create a query that lists every User and the users' transactionId's in an additional column. Every user can have multiple transactions where User.userId = Transaction.userId. When I use the query below I receive an error where it does not recognise the alias U1 for User:

Error Code: 1054. Unknown column 'U1.userId' in 'where clause'

The subquery sub1 is used to narrow down the results that will be concatenated by GROUP_CONCAT.

There are similar questions that cover aliases not being found but none of them in a sub-sub-query for GROUP_CONCAT.

SELECT
    *,
    (
        SELECT
            GROUP_CONCAT(transactionId SEPARATOR ',')
        FROM (
            SELECT * FROM Transaction AS T1
            WHERE T1.userId = U1.userId
        ) as sub1
    ) as transactionIds
FROM
    User as U1

Does anyone know how to achieve the desired result? Rows of Users with an additional column summarising the users' transactionIds.

Akina · Accepted Answer · 2021-04-01T12:16:46.547

1

Subquery not needed.

SELECT
    *,
    (
        SELECT
            GROUP_CONCAT(transactionId SEPARATOR ',')
        FROM Transaction AS T1
            WHERE T1.userId = U1.userId
    ) as transactionIds
FROM
    User as U1

I've included the sub-query because I was looking for a way to LIMIT the results in case I would not need all of the id's.

SELECT
    *,
    (
        SELECT
            SUBSTRING_INDEX(GROUP_CONCAT(transactionId ORDER BY transactionId SEPARATOR ','), ',', needed_amount)
        FROM Transaction AS T1
            WHERE T1.userId = U1.userId
    ) as transactionIds
FROM
    User as U1

Do not forget to adjust @@group_concat_max_len to reasonable but sufficient value.

edited Apr 01 '21 at 12:16

answered Apr 01 '21 at 12:03

Akina

39,301
5
14
25

That seems to do the trick, I've included the sub-query because I was looking for a way to LIMIT the results in case I would not need all of the id's. This solution was taken from https://stackoverflow.com/a/44926956/2099823. Would this mean that you can just use LIMIT without using the sub-query? – Gijs Apr 01 '21 at 12:12
@Gijs Updated. `needed_amount` is used instead of LIMIT. Moreover, it can be not constant but user-defined variable or even an expression (giving different IDs amount in different rows). – Akina Apr 01 '21 at 12:17

Table alias not found in GROUP CONCAT sub-query used to narrows down concat results

1 Answers1