mysql - getting row counts from multiple tables -

i trying row counts returned different tables based on user_id value. users table of users unique column of user_id. other tables have corresponding user_id column join on with.

i think easy, reason cannot counts return right.

what want accomplish alerts = ? , locations = ? ? total number of rows in table user_id = 1,2,3,4,5,6,7, or 8.

$stmt = $db->prepare("     select         count(t_alerts.user_id) alerts,         count(t_locations.user_id) locations     users     left join         (select user_id         alert_logs         alert_logs.event_title '%blocked%'         ) t_alerts             on t_alerts.user_id = users.user_id      left join         (select user_id         location_logs         ) t_locations             on t_locations.user_id = users.user_id      users.user_id in(1,2,3,4,5,6,7,8) ");  $stmt->execute();  //get results $results = $stmt->fetch(pdo::fetch_assoc); 

edit :

a bit of modification eliminate need of supplying in values... use in other queries results 'active' users...

$stmt = $db->prepare("     select         (select count(*)         alert_logs al         event_title '%blocked%' , al.user_id = u.user_id         ) alerts,         (select count(*)         location_logs ll         ll.user_id = u.user_id         ) locations             ( select account_id, computer_id             computers             account_id = :account_id             order computer_id asc limit 0, :licenses         ) c         inner join users u             on u.computer_id = c.computer_id ");  $binding = array(     'account_id' => $_session['user']['account_id'],     'licenses' => $_session['user']['licenses'] ); $stmt->execute($binding); 

i running problem mentioned below statement... returning array of counts per user rather counts combined 1 result.

array (     [0] => array         (             [alerts] => 6             [locations] => 4         )      [1] => array         (             [alerts] => 3             [locations] => 5         )      [2] => array         (             [alerts] => 1             [locations] => 4         )      [3] => array         (             [alerts] => 0             [locations] => 0         )      [4] => array         (             [alerts] => 0             [locations] => 0         )      [5] => array         (             [alerts] => 0             [locations] => 0         )      [6] => array         (             [alerts] => 0             [locations] => 0         )      [7] => array         (             [alerts] => 0             [locations] => 0         )  ) 

what can 'combine' results?

the problem alerts multiplying locations. so, if there 10 alerts , 5 locations, result 50 rows. gets counted.

the easy solution use count(distinct):

select     count(distinct t_alerts.user_id) alerts,     count(distinct t_locations.user_id) locations . . .  

the better solution use subquery counting along each dimension, , join results together.

edit:

in case, nested subqueries in select might best approach, because query filters on users:

select (select count(*)         alert_logs al         event_title '%blocked%' ,               al.user_id = u.user_id        ) alerts,        (select count(*)         location_logs ll         ll.user_id = u.user_id        ) locations users u u.user_id in (1,2,3,4,5,6,7,8) 

edit ii:

i see, there no group by @ end of query. in case, might do:

select (select count(*)         alert_logs al         event_title '%blocked%' ,               al.user_id in (1,2,3,4,5,6,7,8)        ) alerts,        (select count(*)         location_logs ll         ll.user_id in (1,2,3,4,5,6,7,8)        ) locations; 

you don't need users table @ all.