HOW TO ACCESS OTHER JOIN ID ON LEFT JOIN SUBQUERY-CodePudding

Hello i have this query and I need to access de artist_id inside subquery. but don't work.

SELECT g.name, SUM(l.month_start_value), GROUP_CONCAT(l.artist_id), GROUP_CONCAT(l.month_start_value)
FROM genre g
LEFT JOIN genre_artist ga ON g.id = ga.genre_id
LEFT JOIN artist a ON ga.artist_id = a.id
LEFT JOIN (SELECT artist_id,
       (SELECT CAST(value as SIGNED)
        FROM platform_information_artist
        WHERE platform_information_id =
              (SELECT id from platform_information WHERE platform = 'spotify' AND information = 'monthly_listeners')
          and artist_id = a.id
          AND DATE(date) >= DATE(NOW()) - INTERVAL 30 DAY
        ORDER BY date ASC
        LIMIT 1) as month_start_value
FROM platform_information_artist
GROUP BY platform_information_artist.artist_id) l ON a.id = l.artist_id
GROUP BY g.id
ORDER BY g.id ASC;

The line that's wrong is and artist_id = a.id

Returns this error: [42S22][1054] Unknown column 'a.id' in 'where clause'

I don't know why I can't access to a.id inside subquery.

CodePudding user response：

There's a number of ways to do this. The least change from your current query is to simply change a couple of references:

SELECT  g.name AS genre, 
        SUM(l.month_start_value) AS SumStartValue, 
        GROUP_CONCAT(l.artist_id) AS ArtistIDs, 
        GROUP_CONCAT(l.month_start_value) AS MonthStartValues
FROM    genre g
        LEFT JOIN genre_artist AS ga 
            ON g.id = ga.genre_id
        LEFT JOIN artist AS a 
            ON ga.artist_id = a.id
        LEFT JOIN 
        (   SELECT  pia.artist_id,
                    (   SELECT  CAST(pia2.value as SIGNED)
                        FROM    platform_information_artist AS pia2
                        WHERE   pia2.platform_information_id = (SELECT id 
                                                                FROM platform_information 
                                                                WHERE platform = 'spotify' 
                                                                AND information = 'monthly_listeners')
                        AND     pia2.artist_id = pia.artist_id 
                                             --  ^^ Updated from `a.id` to `pia.artist_id`
                        AND     DATE(pia2.date) >= DATE(NOW()) - INTERVAL 30 DAY
                        ORDER BY pia2.date ASC
                        LIMIT 1
                    ) as month_start_value
            FROM    platform_information_artist AS pia 
            GROUP BY pia.artist_id
        ) l 
            ON a.id = l.artist_id
GROUP BY g.id
ORDER BY g.id ASC;

This version should work in most versions of MySQL.

If you are using newer versions of MySql though you have a couple more options, which are both more efficient than the above. The first is to use a lateral join:

SELECT  g.name AS genre, 
        SUM(l.month_start_value) AS SumStartValue, 
        GROUP_CONCAT(a.id) AS ArtistIDs, 
        GROUP_CONCAT(l.month_start_value) AS MonthStartValues
FROM    genre g
        LEFT JOIN genre_artist AS ga 
            ON g.id = ga.genre_id
        LEFT JOIN artist AS a 
            ON ga.artist_id = a.id
        LEFT JOIN LATERAL 
        (   SELECT  CAST(pia.value as SIGNED) AS month_start_value
            FROM    platform_information_artist AS pia
                    INNER JOIN platform_information AS i
                        ON i.id = pia.platform_information_id
            WHERE   i.platform = 'spotify' 
            AND     i.information = 'monthly_listeners'
            AND     pia.artist_id = a.id
            AND     DATE(pia.date) >= DATE(NOW()) - INTERVAL 30 DAY
            ORDER BY pia.date ASC
            LIMIT 1
        ) AS l
           ON 1= 1
GROUP BY g.id
ORDER BY g.id ASC;

The other is to use ROW_NUMBER() rather than limit to select one value per artist:

SELECT  g.name AS genre, 
        SUM(l.month_start_value) AS SumStartValue, 
        GROUP_CONCAT(l.artist_id) AS ArtistIDs, 
        GROUP_CONCAT(l.month_start_value) AS MonthStartValues
FROM    genre g
        LEFT JOIN genre_artist AS ga 
            ON g.id = ga.genre_id
        LEFT JOIN artist AS a 
            ON ga.artist_id = a.id
        LEFT JOIN 
        (   SELECT  pia.artist_id,
                    CAST(pia.value as SIGNED) AS month_start_value,
                    ROW_NUMBER() OVER(PARTITION BY pia.artist_id ORDER BY pia.Date) AS RowNum
            FROM    platform_information_artist AS pia
                    INNER JOIN platform_information AS i
                        ON i.id = pia.platform_information_id
            WHERE   i.platform = 'spotify' 
            AND     i.information = 'monthly_listeners'
            AND     DATE(pia.date) >= DATE(NOW()) - INTERVAL 30 DAY
        ) AS l
            ON l.artist_id = a.id
            AND l.RowNum = 1
GROUP BY g.id
ORDER BY g.id ASC;

Examples of all 3 on db<>fiddle

CodePudding user response：

It looks like you're trying to reference the a.id column in the subquery, but the a table is not defined in the subquery. This is why you're getting the Unknown column 'a.id' in 'where clause' error.

One possible solution is to move the subquery into the FROM clause, and then join it to the artist table. This way, you'll be able to reference the artist table's columns in the subquery. Here's an example of how you might do this:

SELECT g.name, SUM(l.month_start_value), GROUP_CONCAT(l.artist_id), GROUP_CONCAT(l.month_start_value)
FROM genre g
LEFT JOIN genre_artist ga ON g.id = ga.genre_id
LEFT JOIN artist a ON ga.artist_id = a.id
LEFT JOIN (
  SELECT pia.artist_id,
         (SELECT CAST(value as SIGNED)
          FROM platform_information_artist
          WHERE platform_information_id =
                (SELECT id from platform_information WHERE platform = 'spotify' AND information = 'monthly_listeners')
            and artist_id = a.id
            AND DATE(date) >= DATE(NOW()) - INTERVAL 30 DAY
          ORDER BY date ASC
          LIMIT 1) as month_start_value
  FROM platform_information_artist pia
  INNER JOIN artist a ON pia.artist_id = a.id
) l ON a.id = l.artist_id
GROUP BY g.id
ORDER BY g.id ASC;

In the subquery, I've added an INNER JOIN clause that joins the platform_information_artist table to the artist table. This allows you to reference the artist table's id column in the subquery's WHERE clause.

Note that this is just one possible solution to your problem. Depending on your specific needs, there may be other ways to solve this issue as well.