Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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
share|improve this answer
    
Thanks man for your help :) –  M Khalid Junaid Jun 19 '13 at 9:21
add comment

Move the GROUP BY to between the WHERE and the HAVING.

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                
GROUP BY a.`id`
HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) 
    AND `Disable` !='1'   
ORDER BY a.`id`  

Also, move anything that needs to be checked BEFORE the GROUP BY, into the WHERE

Since you are using the HAVING to filter on an aliased subquery, you have should either (option A) add the whole subqueries into the where clause OR (option B?) why don't you just join the table used in the subquery?

Option A Example WHERE clause:

WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0 
    AND (
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) != '' OR 
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) != '' OR 
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`)  != ''
        )
    and (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`)  != '1'
GROUP BY a.`id`
-- REMOVED THE HAVING
ORDER BY a.`id` 

Option B Revised with a LEFT JOIN:

NOTE: This option below has just been roughly put together without any tables / data, so may need some adjustment. Further suggestion to improve this are welcome!

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`,
    case when perms.`permission` = 'Readonly' then perms.status end as ReadOnly, 
    case when perms.`permission` = 'Buy' then perms.status end as Buy, 
    case when perms.`permission` = 'Sell' then perms.status end as Sell, 
    case when perms.`permission` = 'Disable' then perms.status end 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 JOIN `bf_account_permissions` perms on (a.id = perms.id) 
        and (perms.pid = ap.`pid`)
        and (perms.uid=2)
WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
    and (perms.status !='' or (perms.`permission` = 'Disable' and perms.status !='1'))
GROUP BY a.`id`
ORDER BY a.`id`  
share|improve this answer
    
No i want GROUP BY the a.id after the HAVING clause –  M Khalid Junaid Jun 19 '13 at 8:43
1  
@dianuj That would be incorrect syntax. Read my edit. If there is anything you want to check BEFORE the GROUP BY, you must move it into the WHERE –  RoKa Jun 19 '13 at 8:45
    
How do i check the conditions of alias with where clause –  M Khalid Junaid Jun 19 '13 at 8:55
    
@dianuj I have updated with 2 options to do this –  RoKa Jun 19 '13 at 9:15
1  
You option A works fine but it is taking to much time while running subqueries twice its complicated and option 2 is empty if you check the answer of peterm its perfect and taking the original time as before my query was taking But many many thanks for your help and +1 for you –  M Khalid Junaid Jun 19 '13 at 9:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.