user3351213 user3351213 - 5 months ago 8
SQL Question

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.

Answer

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