CODEWITHSUNDEEP
mysqlsql
Sam Samson
Sam Samson

Reputation: 947

#1054 - Unknown column 'XXXX' in 'where clause'

How do you refer parent query column in subquery ? Here is my query

    SELECT 
    c.username, c.staff_of, CONCAT(c.first_name, ' ', c.middle_name, ' ', c.last_name ) AS csr, 
    d.date, d.account, d.total_transaction, d.total_participate, d.total_amount,
    (SELECT COUNT(id) FROM users WHERE created_by = c.username ) AS total_agents
FROM `users` c,   
    (SELECT
          DATE(res_date) AS date, B.res_account AS account,
          COUNT(B.res_id) AS total_transaction,
          COUNT(DISTINCT(B.res_account)) AS total_participate,
          SUM(B.res_amount) AS total_amount
     FROM
          merchant_responses B
     WHERE B.res_account IN (SELECT t.staff_of FROM users t WHERE t.created_by = c.username)
     ) AS d
WHERE c.account_type ='DSO' GROUP BY c.username

and this is the result am getting

#1054 - Unknown column 'c.username' in 'where clause'

How do I make c.username visible in a subquery?

Thanx in advance

Upvotes: 1

Views: 1398

Answers (2)

ysrb
ysrb

Reputation: 6740

That's not subquery it is a derived table.

 SELECT 
    c.username, c.staff_of, CONCAT(c.first_name, ' ', c.middle_name, ' ', c.last_name ) AS csr, 
    d.date, d.account, d.total_transaction, d.total_participate, d.total_amount,
    (SELECT COUNT(id) FROM users WHERE created_by = c.username ) AS total_agents
FROM `users` c,   
    (SELECT
          DATE(res_date) AS date, B.res_account AS account,
          COUNT(B.res_id) AS total_transaction,
          COUNT(DISTINCT(B.res_account)) AS total_participate,
          SUM(B.res_amount) AS total_amount
     FROM
          merchant_responses B
     WHERE B.res_account IN (SELECT t.staff_of FROM users t)
     ) AS d
WHERE c.account_type ='DSO' AND d.account = c.staff_of GROUP BY c.username

Updated Updated the query

Upvotes: 3

Nagasaki
Nagasaki

Reputation: 58

why using a subtable? you use aggregate function SUM but there is no group by statement, i think you can write this like that

    SELECT c.username, 
           c.staff_of, 
           CONCAT(c.first_name, ' ', c.middle_name, ' ', c.last_name ) AS csr, 
           DATE(B.res_date) AS date, 
           B.res_account AS account, 
           COUNT(B.res_id) AS total_transaction, 
           COUNT(DISTINCT(B.res_account)) AS total_participate, 
           SUM(B.res_amount) AS total_amount,
           (SELECT COUNT(id) FROM users WHERE created_by = c.username ) AS total_agents
    FROM `users` c, merchant_responses B
    WHERE B.res_account IN (SELECT t.staff_of FROM users t WHERE t.created_by = c.username) 
    AND c.account_type ='DSO' 
    GROUP BY c.username

A join between c and B is missing, i can't add it because i don't know the keys

Upvotes: 2

Related Questions