2

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

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118

3 Answers3

4

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
peterm
  • 91,357
  • 15
  • 148
  • 157
  • can you post your answer here as well then i will accept there too http://dba.stackexchange.com/questions/44819/how-to-use-group-by-after-the-having-clause-mysql – M Khalid Junaid Jun 19 '13 at 09:12
1

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'
Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
  • I want to check all the conditions before `GROUP BY` – M Khalid Junaid Jun 19 '13 at 08:53
  • 1
    Then use the clauses in the where clause, not in HAVING, because the purpose of having is to filter the selection in a GROUP – Sashi Kant Jun 19 '13 at 09:41
  • How do i check the alias in `where` , `aliases` are checked by `having` – M Khalid Junaid Jun 19 '13 at 10:02
  • You use HAVING to check the results of aggregate functions normally rather than alias fields. Trouble is you have sub selects in the main select, which will likely be slow and which you would need to repeat again within the select to check the results. Also why do you have the GROUP BY when you are not aggregating any fields? All it appears to be doing is the same as a DISTINCT. – Kickstart Jun 19 '13 at 10:10
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
Kickstart
  • 21,403
  • 2
  • 21
  • 33