Clever Geek Handbook

Pass value to fit

        SELECT
            u.*,
            GROUP_CONCAT(DISTINCT f.shot_id SEPARATOR ",") AS ownFavorites,
            GROUP_CONCAT(DISTINCT st.shot_id SEPARATOR ",") AS ownStars,
            GROUP_CONCAT(DISTINCT s.id SEPARATOR ",") AS ownShots,
            ( SELECT AVG(p.count)
                FROM points p
                LEFT JOIN shots s ON s.user_id = **U.ID** AND p.shot_id = s.id
                WHERE date >= DATE_SUB(CURDATE(),INTERVAL 2 DAY)
            ) AS attention,
            ( SELECT SUM(p.count)
                FROM points p
                LEFT JOIN shots s ON s.user_id = **U.ID** AND s.id = p.shot_id
            ) AS popularity
        FROM users u
        LEFT OUTER JOIN shots s ON s.user_id = u.id
        LEFT OUTER JOIN favorites f ON f.user_id = u.id
        LEFT OUTER JOIN stars st ON st.user_id = u.id
        WHERE u.username = ?;

I have two sub-queries that use the u.id parameter (tagged in the query). If I do sql like this it generates something like this:

#1054 - Unknown column 'u.id' in 'on clause'

So the identifier is u.id

NOT defined in SubSelects

. But in MainSelect

I choose from the table users

where it u.id

exists.

My question is, is there a way to pass the selected value u.id

in SubSelects

with shared sql?

+3


source to share


3 answers


Don't forget GROUP BY in subqueries:



SELECT
    u.*,
    COALECSE(a.average, 0) attention,
    COALESCE(p.total, 0) popular,
    GROUP_CONCAT(DISTINCT f.shot_id) AS ownFavorites,
    GROUP_CONCAT(DISTINCT st.shot_id SEPARATOR ",") AS ownStars,
    GROUP_CONCAT(DISTINCT s.id SEPARATOR ",") AS ownShots
FROM
    users u
LEFT JOIN
    (
    SELECT
        s.user_id,
        AVG(p.count) average
    FROM
        shots s
    JOIN
        points p
        ON s.id = p.shot_id
    WHERE
        s.date >+ CURRENT_DATE - INTERVAL 2 DAY
    GROUP BY s.user_id
    ) a
    ON u.id = a.user_id
LEFT JOIN
    (
    SELECT
        s.user_id,
        SUM(p.count) total
    FROM
        shots s
    JOIN
        points p
        ON s.id = p.shot_id
    GROUP BY s.user_id
    ) p
    ON u.id = p.user_id
LEFT OUTER JOIN shots s ON s.user_id = u.id
LEFT OUTER JOIN favorites f ON f.user_id = u.id
LEFT OUTER JOIN stars st ON st.user_id = u.id
WHERE u.username = 'user'
+2


source


Try to include selections on a subgrid connection.

FROM users u
LEFT OUTER JOIN shots s ON s.user_id = u.id
LEFT OUTER JOIN favorites f ON f.user_id = u.id
LEFT OUTER JOIN stars st ON st.user_id = u.id
LEFT OUTER JOIN ( SELECT AVG(p.count) AverageOfP, p.shot_id
            FROM points p             
            WHERE date >= DATE_SUB(CURDATE(),INTERVAL 2 DAY)
        ) p ON p.shot_id = s.id
LEFT OUTER JOIN ( SELECT SUM(p.count) SumOfP, p.shot_id
            FROM points p             
        ) p2 ON p2.shot_id = s.id


Table s is already joined to u and should be good. Then, in your choice, you can simply select AverageOfP and SumOfP.

+1


source


It looks like it might work. The selection has no information about the users table in the way you did. I believe that users would know that.

    SELECT
        u.*,
        GROUP_CONCAT(DISTINCT f.shot_id SEPARATOR ",") AS ownFavorites,
        GROUP_CONCAT(DISTINCT st.shot_id SEPARATOR ",") AS ownStars,
        GROUP_CONCAT(DISTINCT s.id SEPARATOR ",") AS ownShots,
        A.Attention, P.Popularity
      FROM users u
    LEFT OUTER JOIN shots s ON s.user_id = u.id
    LEFT OUTER JOIN favorites f ON f.user_id = u.id
    LEFT OUTER JOIN stars st ON st.user_id = u.id
    LEFT OUTER JOIN 
      ( SELECT AVG(p.count) attention
            FROM points p
            LEFT JOIN shots s ON s.user_id = **U.ID** AND p.shot_id = s.id
            WHERE date >= DATE_SUB(CURDATE(),INTERVAL 2 DAY)
        ) AS A,
        ( SELECT SUM(p.count) popularity
            FROM points p
            LEFT JOIN shots s ON s.user_id = **U.ID** AND s.id = p.shot_id
        ) AS P
    WHERE u.username = ?;
+1


source






More articles:


All Articles