CODEWITHSUNDEEP
mysqljoin
300
300

Reputation: 1031

Join two mysql queries and include column values with zero count as well

I have two MySQL tables

desc table_name1;   
Field, Type, Null, Key, Default, Extra
'id', 'int(11)', 'NO', 'PRI', '0', ''
'name', 'varchar(100)', 'YES', '', NULL, ''
'active', 'int(1)', 'YES', '', NULL, ''

desc table_name2;
Field, Type, Null, Key, Default, Extra
'date', 'date', 'NO', 'PRI', '0000-00-00', ''
'col_name1', 'varchar(40)', 'YES', '', '0', ''
'host', 'varchar(100)', 'NO', '', '', ''
'ip', 'varchar(100)', 'NO', 'PRI', '', ''
'snapdate', 'date', 'YES', '', NULL, ''
'col_name2', 'int(11)', 'NO', 'PRI', '0', ''

And two sql queries: sql1: 

select id, name from .table_name1 where active = 1 and id not in (2954,2914) order by name;

sql2: 

select count(*) from .table_name2 where date= '2017-07-03' and col_name2 = $code; #$code = values in id column from sql1

So sql1 returns 9 rows then sql2 is run 9 times (for each value in column id from sql1)

What I want to do: I wanted to join these two queries so that I can run a single query and still get the same result. And I want the result in for of a table with two columns:

name   count
value1 89
value2 184
value3 528

What I have tried so far: So I used inner join and came up with following sql: sql3:

    select d.name, count(m.col_name2) as count
      from .table_name1 as d
inner join .table_name2 as m
        on d.id = m.col_name2
     where d.active = 1
       and d.id not in (2954,2914)
       and m.date = '2017-07-03'
  group by d.name
  order by d.name;

Problem I am facing:
Though above sql3 does what I want but it skips those values in id column (from sql1) which would return zero as count. (when used in sql2).

For example if sql1 is returing 9 rows then the sql3 is returing only 8 rows as it is skipping that one value which will have count as zero.

I have tried using CASE (and removed "and m.date = '2017-07-03'"):

select d.name, case count(m.col_name2) when m.date = '2017-07-03' then 1 else 0 end as count

Also, I have tried using if (and removed "and m.date = '2017-07-03'"):

select d.name, if (m.date = '2017-07-03', count(m.col_name2), 0) as count

But both above attempts give 8 rows (same as sql3) but the count is returned zero.

My question: What am I missing while using CASE or if? How can I get this missing value in the result as well with a count of zero for it using modified sql3 or any other single query?

Upvotes: 1

Views: 1187

Answers (1)

Dimgold
Dimgold

Reputation: 2944

You are missing zero values since they dont have a match in the inner join. Try left join instead:

  select d.name, count(m.col_name2) as count
  from .table_name1 as d
  left join .table_name2 as m
  on d.id = m.col_name2
  where d.active = 1
   and d.id not in (2954,2914)
   and (m.date = '2017-07-03' or m.date is Null)
  group by d.name
  order by d.name;

Note that m.date condition is combined with Null, in case there are no values per this name.

Upvotes: 1

Related Questions