CODEWITHSUNDEEP
mysqlsql
Faruk Omar
Faruk Omar

Reputation: 1193

Error Code: 1054 Unknown column in 'where clause'

I want to fetch total views of every artist all contents

wp_content_details.artist_id = wp_artist.id is generating the error 'Unknown column in where clause' when i use static value wp_content_details.artist_id = 29 then its ok but i need to use the condition wp_content_details.artist_id = wp_artist.id

SELECT wp_artist.id
    ,wp_artist.NAME
    ,(
        SELECT SUM(total_user) AS total_view
        FROM (
            SELECT COUNT(DISTINCT (user_id)) AS total_user
            FROM wp_views
            LEFT JOIN wp_content_details ON wp_content_details.content_id = wp_views.content_id
            WHERE wp_content_details.artist_id = wp_artist.id /* Its ok if use:  wp_content_details.artist_id = 29*/
                AND wp_content_details.role_id = 2
            GROUP BY wp_views.content_id
            ) content_views
        ) AS total_views
FROM wp_artist
WHERE wp_artist.id IN (
        SELECT DISTINCT (wp_content_details.artist_id)
        FROM wp_content_details
        WHERE wp_content_details.artist_id = wp_artist.id
            AND wp_content_details.role_id = 2
        )
ORDER BY total_views DESC
    ,wp_artist.NAME ASC

Please help me. If you have any confusion please ask me.

Upvotes: 1

Views: 3382

Answers (1)

Ewan
Ewan

Reputation: 1087

wp_artist.ID is out of scope. Try

    SELECT SUM(total_user) AS total_view
    FROM (
        SELECT COUNT(DISTINCT (user_id)) AS total_user, wp_content_details.artist_id
        FROM wp_views
        LEFT JOIN wp_content_details ON wp_content_details.content_id = wp_views.content_id
        WHERE wp_content_details.role_id = 2
        GROUP BY wp_views.content_id, wp_content_details.artist_id 
        ) content_views
Where content_views.artist_id = wp_artist.id
    ) AS total_views

Upvotes: 1

Related Questions