Strange Behaviour of Group by in Query which needs to be optimized

Question

Can anyone one help me optimize this query

SELECT 
  `debit_side`.`account_code` CODE,
  GROUP_CONCAT(DISTINCT accounts.name) AS DebitAccount,
  GROUP_CONCAT(debit_side.amount) AS DebitAmount,
  GROUP_CONCAT(transaction_info.voucher_date) AS DebitVoucherDate,
  (SELECT 
    GROUP_CONCAT(DISTINCT accounts.name) 
  FROM
    (accounts) 
    LEFT JOIN debit_side 
      ON accounts.code = debit_side.account_code 
    LEFT JOIN credit_side 
      ON debit_side.transaction_id_dr = credit_side.transaction_id_cr 
    LEFT JOIN transaction_info 
      ON transaction_info.transaction_id = credit_side.transaction_id_cr 
  GROUP BY credit_side.account_code 
  HAVING credit_side.account_code = `Code`) AS CreditAccount,
  (SELECT 
    GROUP_CONCAT(credit_side.amount) AS CreditAmount 
  FROM
    (accounts) 
    LEFT JOIN debit_side 
      ON accounts.code = debit_side.account_code 
    LEFT JOIN credit_side 
      ON debit_side.transaction_id_dr = credit_side.transaction_id_cr 
    LEFT JOIN transaction_info 
      ON transaction_info.transaction_id = credit_side.transaction_id_cr 
  GROUP BY credit_side.account_code 
  HAVING credit_side.account_code = `Code`) AS CreditAmount,
  (SELECT 
    GROUP_CONCAT(transaction_info.voucher_date) AS CreditVoucherDate 
  FROM
    (accounts) 
    LEFT JOIN debit_side 
      ON accounts.code = debit_side.account_code 
    LEFT JOIN credit_side 
      ON debit_side.transaction_id_dr = credit_side.transaction_id_cr 
    LEFT JOIN transaction_info 
      ON transaction_info.transaction_id = credit_side.transaction_id_cr 
  GROUP BY credit_side.account_code 
  HAVING credit_side.account_code = `Code`) AS CreditVoucherDate 
FROM
  (`accounts`) 
  LEFT JOIN `credit_side` 
    ON `accounts`.`code` = `credit_side`.`account_code` 
  LEFT JOIN `debit_side` 
    ON `debit_side`.`transaction_id_dr` = `credit_side`.`transaction_id_cr` 
  LEFT JOIN `transaction_info` 
    ON `transaction_info`.`transaction_id` = `credit_side`.`transaction_id_cr` 
GROUP BY `debit_side`.`account_code` 
HAVING `Code` IS NOT NULL 
ORDER BY `debit_side`.`account_code` ASC 

Actually in this query i am trying to get data for debit side and credit side for all accounts. You must have noticed that sub queries are repeated but selecting different columns. This query is fetching perfect results but i want it to be optimized. Here is the link to my schema

http://www.sqlfiddle.com/#!2/82274/6

Previously i had these two queries which i tried to combine

SELECT
  debit_side.account_code    DebitCode,
  group_concat(distinct accounts.name) as DebitAccount,
  group_concat(debit_side.amount) as DebitAmount,
  group_concat(transaction_info.voucher_date) as DebitVoucherDate
FROM (`accounts`)
  LEFT JOIN `credit_side`
    ON `accounts`.`code` = `credit_side`.`account_code`
  LEFT JOIN `debit_side`
    ON `debit_side`.`transaction_id_dr` = `credit_side`.`transaction_id_cr`
  LEFT JOIN `transaction_info`
    ON `transaction_info`.`transaction_id` = `credit_side`.`transaction_id_cr`
GROUP BY `debit_side`.`account_code`
ORDER BY `debit_side`.`account_code` ASC

And

SELECT
  credit_side.account_code    CreditCode,
  group_concat(distinct accounts.name) as CreditAccount,
  group_concat(credit_side.amount) as CreditAmount,
  group_concat(transaction_info.voucher_date) as CreditVoucherDate
FROM (`accounts`)
  LEFT JOIN `debit_side`
    ON `accounts`.`code` = `debit_side`.`account_code`
  LEFT JOIN `credit_side`
    ON `debit_side`.`transaction_id_dr` = `credit_side`.`transaction_id_cr`
  LEFT JOIN `transaction_info`
    ON `transaction_info`.`transaction_id` = `credit_side`.`transaction_id_cr`
GROUP BY `credit_side`.`account_code`
ORDER BY `credit_side`.`account_code` ASC

Also i want to remove null record which is being fetched. Note : You should also note that in the sub queries i am using a little bit different conditions which are resulting according to my requirements.

EDITS

I have covercome the problem to remove the null record but optimization is left still.

NEW EDITS

Here is what i tried with semi join

SELECT
  `lds`.`account_code`    DebitCode,
  group_concat(distinct la.name) as DebitAccount,
  group_concat(lds.amount) as DebitAmount,
  group_concat(lti.voucher_date) as DebitVoucherDate,
  `rcs`.`account_code`    CreditCode,
  group_concat(distinct ra.name) as CreditAccount,
  group_concat(rcs.amount) as CreditAmount,
  group_concat(rti.voucher_date) as CreditVoucherDate
FROM accounts as la
  LEFT join accounts as ra
    ON ra.`code` = la.`code`
  LEFT JOIN `credit_side` as lcs
    ON `la`.`code` = `lcs`.`account_code`
  LEFT JOIN `debit_side` as lds
    ON `lds`.`transaction_id_dr` = `lcs`.`transaction_id_cr`
  LEFT JOIN `transaction_info` as lti
    ON `lti`.`transaction_id` = `lcs`.`transaction_id_cr`
  LEFT JOIN `debit_side` as rds
    ON `ra`.`code` = `rds`.`account_code`
  LEFT JOIN `credit_side` rcs
    ON `rds`.`transaction_id_dr` = `rcs`.`transaction_id_cr`
  LEFT JOIN `transaction_info` as rti
    ON `rti`.`transaction_id` = `rcs`.`transaction_id_cr`
GROUP BY `CreditCode`
HAVING `CreditCode` IS NOT NULL
ORDER BY `CreditCode` ASC

The strange thing is that if i change group by having and order by with DebitCode it bring perfect records for debit side and if i change this with CreditCode if brings perfect records for credit side. Is there any way to overcome this problem or any alternative.

Answer 1

I've been looking into your schema and SQL for a while and I don't quite understand your logic. Things as I see them:

• you have a set of transactions (9 to be precise);
• for each transaction you have details on the debit and credit sides;
• using the account_code on each side, you can obtain info about accounts.

So, I would go this way for starters and created a VIEW, that would provide you with all the necessary information bout your transactions. I have used INNER joins here, as I believe each transaction must have both, debit and credit sides, and each side, in turn, should have an account:

CREATE VIEW all_transactions AS
SELECT ti.transaction_id tid, ti.voucher_no tvno, ti.voucher_date tvdt,
       ds.account_code dacc, ds.amount damt, da.name daname, da.type dat,
       cs.account_code cacc, cs.amount camt, ca.name caname, ca.type cat
  FROM transaction_info ti
  JOIN debit_side ds ON ds.transaction_id_dr = ti.transaction_id
  JOIN credit_side cs ON cs.transaction_id_cr = ti.transaction_id
  JOIN accounts da ON da.code = ds.account_code
  JOIN accounts ca ON ca.code = cs.account_code;

Now, looking at your queries, it seems that you're trying to get a list of all counter-side operations for each account code. I'm not sure what's the purpose of this, but I would do the following:

• selected a list of unique account codes;
• created an aggregated list of debit-side operations for each account code, where such code was on the credit side;
• created same aggregated list for credit-side operations, where such account was on debit side;
• and put each account code in the middle.

So something like this might do the job:

SELECT group_concat(dacc) "D-Accounts",
       group_concat(damt) "D-Amounts",
       group_concat(daname) "D-Names",
       group_concat(dvdt) "D-Dates",
       code, name,
       group_concat(cacc) "C-Accounts",
       group_concat(camt) "C-Amounts",
       group_concat(caname) "C-Names",
       group_concat(cvdt) "C-Dates"
  FROM (
    SELECT atl.dacc, atl.damt, atl.daname, atl.tvdt dvdt,
           a.code, a.name, NULL cacc, NULL camt, NULL caname, NULL cvdt
      FROM accounts a
      LEFT JOIN all_transactions atl ON atl.cacc = a.code
    UNION ALL
    SELECT NULL, NULL, NULL, NULL, a.code, a.name,
           atr.cacc, atr.camt, atr.caname, atr.tvdt cvdt
      FROM accounts a
      RIGHT JOIN all_transactions atr ON atr.dacc = a.code
  ) full_join
 GROUP BY code, name
 ORDER BY code;

In the inner part I'm simulating FULL OUTER join by uniting 2 other joins, LEFT and RIGHT ones. And the outer part performs all the groupings. Take a look at the result.

Note, that if you'd like to add/remove columns from the result, you should modify both, inner and outer queries.

I hope this is what you've been looking for.