CODEWITHSUNDEEP
mysql
user3351213
user3351213

Reputation: 3

How to define Query within LIKE statement

I use this query to match data from two tables but it returns

Error Code: 1242 Subquery returns more than 1 row

my example tables and query are below.

SELECT 
  ACCOUNT_CODE,
  DR_AMOUNT,
  CR_AMOUNT 
FROM
  `tblinvoices` 
WHERE ACCOUNT_CODE LIKE
  CONCAT((
      SELECT 
          LEFT(SUB_GROUP, 4) 
      FROM
          `tblcharts` 
      WHERE ACC_TYPE = 'G'), '%') ;

Temp Table.

SUB_GROUP 
------------------
1-01                
2-01                
3-01                
4-01                
6-01                
6-02                
6-03                
7-01                
7-02                
8-01                
9-01

DATA TABLE TBLINVOICES

ACCOUNT_CODE  DR_AMOUNT  CR_AMOUNT  
------------  ---------  -----------
6-03-0001         27500            0
6-02-0001             0        27500
6-03-0001          1700            0
6-02-0001             0         1700
3-01-0005         15000            0
6-03-0001             0        15000
6-03-0001        315432            0
6-02-0002             0       315432

I want to get all debit and credit amounts where match first 4 character of group code with invoice account code. Please anyone help in this regards.

Upvotes: 0

Views: 26

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you want an aggregation query. It would look something like this:

SELECT LEFT(i.ACCOUNT_CODE, 4) as sub_group, SUM(i.DR_AMOUNT) as DR_AMOUNT, 
       SUM(i.CR_AMOUNT) as CR_AMOUNT
FROM tblinvoices i
GROUP BY LEFT(i.ACCOUNT_CODE, 4);

Upvotes: 1

pablo.vix
pablo.vix

Reputation: 2233

I believe you can do what you want, using a subquery in the like clause BUT you must be sure that the subquery returns only one result: does not make sense to database to compare a single line of tblinvoices to a lot of lines.

You can achieve what you want narrowing the results in subquery's where clause. Also, as long as you comparing just a fraction of the whole field, I believe you should concat '%' characters at the end of the result of the subquery,or it will never match!

Put the data/format of tblcharts so I can help in a more detailed way.

Upvotes: 0

Related Questions