How to get total "active" users using Mysql?

Question

I have four tables:

Table A: users_table

Table B: users_account_table

Table C: fangates_table

Table D: downloads_table

Table A(users_table) contains three columns: ID, NAME and GATE_COUNT. Table C(fangates_table) contains users gates. Table D(downloads_table) contains fangates_table_id and account_id.

TABLE A
ID  |   NAME    |   GATE_COUNT
---------------------------
1   |   Joy     |   2        
---------------------------
2   |   Ash     |   0
---------------------------
3   |   Nik     |   2
---------------------------
4   |   Wox     |   0

TABLE B
ID  |   A_ID    |   ACCOUNT_ID
---------------------------
1   |   1       |   123456       
---------------------------
2   |   1       |   654321
---------------------------
3   |   3       |   5888
---------------------------
4   |   3       |   8787


TABLE C
ID  |   A_ID    |   TITLE
---------------------------
1   |   1       |   ABCD         
---------------------------
2   |   1       |   DFCV
---------------------------
3   |   3       |   FGTG
---------------------------
4   |   3       |   FRGTG

TABLE D
ID  |   C_ID    |   ACCOUNT_ID
---------------------------
1   |   1       |   123456       
---------------------------
2   |   2       |   123456
---------------------------
3   |   3       |   7859
---------------------------
4   |   1       |   7585

From the above tables, I am trying to get the total "active" users (where an active user is defined as a user with fangates having any downloads other not themself)

I have tried the following query, but it has failed.

SELECT COUNT(*) FROM D
WHERE (C_ID,ACCOUNT_ID)
NOT IN 
(SELECT C.ID, B.ACCOUNT_ID FROM A 
LEFT JOIN B ON A.ID = B.A_ID 
LEFT JOIN C ON A.ID = C.A_ID
WHERE A.GATE_COUNT >0);

Answer 1

The error, as I see it, is that you are failing to provide a join parameter in the initial selection of your table. If I am to understand your table design correctly, the following query would retrieve all the information in your tables:

SELECT *
FROM A,B,C,D
WHERE B.A_ID = A.ID AND C.A_ID = A.ID AND D.C_ID = C.ID; 

If my presumption is correct, and you are trying to get the active users, you could simply place your parameters as an appended AND and continue onward. However, doing this many joins could greatly slow down any code; joins are a very taxing process, no matter how small the tables are. Since you are looking to find the count of downloads

SELECT COUNT(*) FROM D
WHERE (C_ID)
NOT IN 
(SELECT C.ID FROM A, C 
WHERE GATE_COUNT >0 AND A.ID = C.A_ID);

From what I can tell, you don't need to specify the distinct ACCOUNT_ID from the B table, as the information is distinctly paired to the B table. Otherwise, your columns would mismatch.

Otherwise, you can do a second lookup and try adding the B lookup as an AND parameter

SELECT COUNT(*) FROM D
WHERE (C_ID)
NOT IN (SELECT C.ID FROM A, C 
WHERE GATE_COUNT >0 AND A.ID = C.A_ID) AND ACCOUNT_ID NOT IN
(SELECT DISTINCT B.ACCOUNT_ID FROM A,B WHERE A.ID = B.A_ID AND A.GATE_COUNT >0);

Answer 2

SELECT COUNT(DISTINCT(B.A_ID))
FROM TABLE_B B
JOIN TABLE_C C ON (B.A_ID = C.A_ID)
JOIN TABLE_D D ON (D.C_ID = C.C_ID AND B.ACCOUNT_ID <> D.ACCOUNT_ID)