Mysql two queries in one (inner join?) -

i need return results of 2 queries within one. i've read other posts , not sure if should inner join.

i these 2 queries:

mariadb [servers]> select * servers; +----+------------+---------+------------+ | id | servername | ip      | returncode | +----+------------+---------+------------+ |  1 | server1    | 0.0.0.0 | 0          | |  2 | server2    | 0.0.0.0 | 1          | |  3 | server2    | 0.0.0.0 | 0          | |  4 | server3    | 0.0.0.0 | 0          | |  5 | server3    | 0.0.0.0 | 4          | |  6 | server3    | 0.0.0.0 | 4          | |  7 | server3    | 0.0.0.0 | 4          | +----+------------+---------+------------+  mariadb [servers]> select servername,count(returncode) servers returncode<>0 group servername; +------------+-------------------+ | servername | count(returncode) | +------------+-------------------+ | server2    |                 1 | | server3    |                 3 | +------------+-------------------+ 

into this:

mariadb [servers]> select ????????? +----+------------+---------+------------+-------------------+ | id | servername | ip      | returncode | count(returncode) | +----+------------+---------+------------+-------------------+ |  1 | server1    | 0.0.0.0 | 0          | 0                 | |  2 | server2    | 0.0.0.0 | 1          | 1                 | |  3 | server2    | 0.0.0.0 | 0          | 1                 | |  4 | server3    | 0.0.0.0 | 0          | 3                 | |  5 | server3    | 0.0.0.0 | 4          | 3                 | |  6 | server3    | 0.0.0.0 | 4          | 3                 | |  7 | server3    | 0.0.0.0 | 4          | 3                 | +----+------------+---------+------------+-------------------+ 

can please me understand how this? feel inner join, i'm not sure understand concept completely..

you can use subquery count of returncode each server, use left join servers table result:

select s.id, s.servername, s.ip, s.returncode,    coalesce(c.totalcount, 0) totalcount servers s left join (   select servername, count(returncode) totalcount   servers   returncode<>0   group servername ) c   on s.servername = c.servername; 

see sql fiddle demo

i chose left join return rows servers table if there not matching row in subquery.