How to use GROUP BY after the Having clause Mysql

Question

Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions

All the accounts which is not Disable and also shows their permissions I got all accounts according to scenario but the problem is if one account has more than one products then it obviously shows account id more than one time

*What i am looking for to GROUP BY the a.id after the HAVING clause which checks the permissions * but no luck getting syntax error

Error Code: 1064 right syntax to use near 'GROUP BY a.`id` LIMIT 0, 1000' at line 14

Here is my query

SELECT  a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
c.`clients_last_name`, a.`accounts_account_name`
,(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
 FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
 INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
 INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
 INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
 WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0                
 HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) 
 AND `Disable` !='1'   ORDER BY a.`id`  GROUP BY a.`id`

Any help would be appreciated

Answer 1

You have corelated subqueries which often perform poorly. Possible to rewrite to use joins, which also makes it obvious you can check the values in the WHERE clause.

Not sure why you are using GROUP BY when you have no aggregate functions.

Something like this (untested)

SELECT  a.id AS aid,
    ap.pid, 
    p.`products_name`, 
    a.accounts_account_number, 
    c.clients_name,
    a.accounts_product_type AS pptype,
    c.`clients_last_name`, 
    a.`accounts_account_name`,
    ReadonlySub.status AS Readonly,
    BuySub.status AS Buy,
    SellSub.status AS Sell,
    DisableSub.status AS `Disable`
FROM bf_clients c 
INNER JOIN bf_user_clients uc ON (c.id = uc.client_id)
INNER JOIN bf_accounts a ON (c.id = a.accounts_clients)
INNER JOIN bf_account_products ap ON (a.id = ap.aid)
INNER JOIN bf_products p ON (ap.pid = p.id)
LEFT OUTER JOIN(SELECT `status` FROM bf_account_permissions WHERE `permission`='Readonly') ReadonlySub
ON ReadonlySub.uid = uc.agt_user_id AND ReadonlySub.aid = a.id AND ReadonlySub.pid = ap.pid
LEFT OUTER JOIN(SELECT `status` FROM bf_account_permissions WHERE `permission`='Buy') BuySub
ON BuySub.uid = uc.agt_user_id AND BuySub.aid = a.id AND BuySub.pid = ap.pid
LEFT OUTER JOIN(SELECT `status` FROM bf_account_permissions WHERE `permission`='Sell') SellSub
ON SellSub.uid = uc.agt_user_id AND SellSub.aid = a.id AND SellSub.pid = ap.pid
LEFT OUTER JOIN(SELECT `status` FROM bf_account_permissions WHERE `permission`='Disable') DisableSub
ON DisableSub.uid = uc.agt_user_id AND DisableSub.aid = a.id AND DisableSub.pid = ap.pid
WHERE uc.agt_user_id=2 
AND a.deleted=0 
AND c.deleted=0      
AND (ReadonlySub.status !='' 
OR BuySub.status !='' 
OR SellSub.status !='' 
OR DisableSub.status !='' )
AND DisableSub.status !='1'     
ORDER BY a.id

Answer 2

You can try to use an outer select

SELECT q.*
  FROM
(
  SELECT a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
         c.`clients_last_name`, a.`accounts_account_name`,
         (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
         (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
         (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
         (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
    FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
   INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
   INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
   INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
   WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0                
  HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) AND `Disable` !='1'   
) q
 GROUP BY aid

Answer 3

HAVING SHOULD BE USED AFTER A GROUP BY

Try this::

SELECT  a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
c.`clients_last_name`, a.`accounts_account_name`
,(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
 FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
 INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
 INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
 INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
 WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0                
    ORDER BY a.`id`  GROUP BY a.`id` HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) 
 AND `Disable` !='1'