Stack Exchange
#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

mate, use JOIN, please. theres no need for all of these sub querys. inefficient and problematic

There is also a join condition missing between the derived table "d" and the users table. This is going to create a cartesian join. Additionally the use of aggregates inside the derived table is illegal in standard SQL. MySQL wil let you get away with it though - but it will return random values: http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html

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

This work pretty much the results that I want, is it possible also to get all the records which does not exist in tables merchant_responses ?

@samdev i don't understand your request

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

also known as an inline view

@ysrb created_by column does not exists in merchant_responses so it gives unknown column error.
