Multiple Table Query

Question

Multiple Table Query

scottd 42951

SSC Enthusiast

Points: 175
More actions
April 13, 2016 at 8:59 am

#306367

Hello all,
I first apologize if this is the wrong section... I could not find a forum that was just for general query questions....
I am having quite a time trying to write an efficient query to get user counts. Here is what I'm trying to do:
We have an accounts table with the following: account_id & account_title
We then have an account_groups table that has account_id, group_id, public_flag & group_title
we then have an account_user_groups table that has account_id, group_id & sub_id
_______________________________
What I would like to do is find out the following:
account 1 --- public_flag(yes = 1) --- sub_id count
account 2 --- public_flag(yes = 1) --- sub_id count
account 3 --- public_flag(yes = 1) --- sub_id count
account 4 --- public_flag(yes = 1) --- sub_id count
account 5 --- public_flag(yes = 1) --- sub_id count
So basically I want to go to each account (by account_id) and total up the amount of distinct sub_id are in the account_user_groups table with group that are flagged yes for public_flag.
Here is an example of what I was messing with but the numbers are all off:
SELECT
(SELECT COUNT(sub_id) FROM account_user_groups AUG WHERE AUG.active = '1' AND AUG.group_id=AG.group_id) AS PUBLIC_COUNT,
(SELECT account_title FROM accounts A WHERE A.active = '1' AND A.account_id=AG.account_id) AS ACCT_TITLE,
AG.group_title, AG.group_id
FROM account_groups AS AG
WHERE AG.sendpublic = '1' AND AG.active = '1' AND account_id IN
(SELECT
account_id
FROM
ACCOUNTS)
ORDER BY AG.group_id DESC
Any assistance would be greatly appreciated!!!!
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Help us help you. Please post DDL and sample data in a consumable form. Learn how to do it from the articles linked in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

MadAdmin SSChampion Points: 11261 More actions · Answer 2

Wild stab as there is no DDL but maybe this.

There could be duplication caused by the joins though but I assume this would be more performant.

SELECT COUNT(distinct AUG.sub_id) AS PUBLIC_COUNT,

ACC.account_title AS ACCT_TITLE, AG.group_title, AG.group_id

FROM account_groups AS AG

INNER JOIN ACCOUNTS ACC on ACC.account_id = AG.account_id and A.active = 1

LEFT JOIN account_user_groups AUG ON AUG.active = 1 AND AUG.group_id=AG.group_id

WHERE AG.sendpublic = 1 AND AG.active = 1

group by account_title, AG.group_title, AG.group_id

ORDER BY AG.group_id DESC

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]