How to define Query within LIKE statement

Question

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.

Gordon Linoff · Accepted Answer · 2014-11-09 12:58:22Z

1

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);

answered Nov 9, 2014 at 12:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pablo.vix · Accepted Answer · 2014-11-09 12:56:31Z

0

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.

answered Nov 9, 2014 at 12:56

pablo.vix

2,2632 gold badges17 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to define Query within LIKE statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related