Únase a la misma table dos veces en una tecla diferente

Tengo dos accounts tablas y subs .
La primera tabla tiene id y la segunda tabla tiene id campos, requester_account_id , grabber_account_id

Quiero el recuento de cuántas requestes secundarias realizó una count, y cuántas capturas hizo (en function de si requester_account_id/grabber_account_id está requester_account_id/grabber_account_id con su id )

En una consulta

salida:

 +------------+----------------+-----------------+ | account_id | subs_requested | subs_grabbed | +------------+----------------+-----------------+ | 1 | 4 | 3 | | 3 | 2 | 1 | +------------+----------------+-----------------+ 

Usa algo como

 select accounts.id, count(distinct s1.id) as num_req, count(distinct s2.id) as num_grab from accounts left join subs as s1 on accounts.id = s1.requester_account_id left join subs as s2 accounts.id = s2.grabber_account_id group by accounts.id 

El truco es usar los subs tabla dos veces: subs as s1 y subs as s2 , cada vez unidos por un campo diferente ….

Nota con respecto a la eficiencia: no estoy seguro, pero creo que esta solución es más rápida que la solución de subconsulta, aunque no probada (al less no será más lenta). Siempre prefiero la left join a la subconsulta siempre que sea posible.

Esto debería funcionar:

 SELECT a.account_id, (SELECT COUNT(*) FROM subs WHERE requester_account_id=a.account_id) AS subs_requested, (SELECT COUNT(*) FROM subs WHERE grabber_account_id=a.account_id) AS subs_grabbed FROM accounts a 

Como entiendo lo que estás buscando, sería algo como esto:

 select a.id as account_id, count(distinct sr.id) as requests, count(distinct gr.id) as grabs from accounts a left outer join subs sr on sr.requester_account_id = a.id left outer join subs gr on gr.grabber_account_id = a.id 

En ocasiones, he experimentado rarezas con MySQL al hacer funciones agregadas en uniones a la izquierda, así que aquí hay otra forma:

 select a.id as account_id, (select count(distinct sr.id) from subs sr where sr.requester_account_id = a.id) as requests, (select count(distinct gr.id) from subs sr where gr.grabber_account_id = a.id) as grabs from accounts a 

Puede join a la tabla subs solo una vez si lo desea (no estoy seguro de que tenga un mejor performance)

 select acc.id sum(if(sub.requester_account_id = acc.id, 1, 0)) as req, sum(if(sub.grabber_account_id = acc.id, 1, 0)) as grab from subs sub, accounts acc where sub.requester_account_id = acc.id or sub.grabber_account_id = acc.id group by acc.id