CODEWITHSUNDEEP
mysql
black_belt
black_belt

Reputation: 6799

Mysql: Unable to fetch value from a select query inside a query

I have the following query. If I run it I get this error message.

Query-

SELECT account_name,ABC,date FROM entries
    LEFT JOIN accounts ON accounts.id = entries.accounts_id
    LEFT JOIN voucher ON voucher.id = entries.trans_id 
    WHERE trans_id IN ( SELECT trans_id, amount AS ABC FROM entries 
                         WHERE accounts_id='$accounts_id' AND side='C') 
    AND accounts_id!='$accounts_id' AND side='D'
    AND voucher.date between '$dateragne1' AND '$dateragne2'

I think the problem is with the value ABC. It is unable to fetch the value from the second query.

Could you please tell me how to fix this query?

Thanks in Advance :)

Upvotes: 1

Views: 85

Answers (1)

Sergei Danielian
Sergei Danielian

Reputation: 5025

Try this:

SELECT account_name, _inner.ABC, date 
  FROM 
  (
     SELECT amount AS ABC FROM entries 
       WHERE accounts_id='$accounts_id' AND side='C'
  ) AS _inner, entries
  LEFT JOIN accounts ON accounts.id = entries.accounts_id
  LEFT JOIN voucher ON voucher.id = entries.trans_id
    WHERE trans_id IN 
    ( 
      SELECT trans_id FROM entries WHERE accounts_id='$accounts_id' AND side='C'
    ) 
AND accounts_id!='$accounts_id' AND side='D'
AND voucher.date between '$dateragne1' AND '$dateragne2'`

Notes:

  • Using subquery like this doesn't allow you to request a fields from it.
  • Also, IN statement use data from only only column, not two.

Upvotes: 1

Related Questions