BeetleJuice BeetleJuice - 4 months ago 9
MySQL Question

SQL Join and Count returns unexpected match

tbl1

type|price
----------
1 | 10
2 | 15


tbl2

type|expires
----------
1 | 2015-01-01
1 | 2017-01-01
1 | 2017-01-01
2 | 2015-01-01
2 | 2017-01-01


I'd like a query that returns data about a given type in
tbl1
, and the number of current (non-expired) records of that type in
tbl2
. Here is my query:

select tbl1.*,
count(if(tbl2.expires > now(),1,null)) current
from tbl1 left join tbl2
on tbl1.type=tbl2.type
where tbl1.type = 1


As expected, it returns:

type|price|current
1 | 10 | 2


However, when I ask for a non existent type, I expect 0 result. However, if I replace the type in the query to
3
, I get:

type|price|current
NULL|NULL | 0


Please help me understand


  1. How does that record match the query? (I expected an empty result set)

  2. How to get the behavior I expect? (ie no result for non-existent
    type
    )



Live demo

Answer

Using an aggregate function like count without a group by clause returns always exactly one row.

A work-around is to wrap your query in a subquery and check for type again in the outer query:

select *
from (
   select tbl1.*,
          count(if(tbl2.expires > now(),1,null)) current
   from tbl1 
   left join tbl2 on tbl1.type=tbl2.type
   where tbl1.type = 3) as t
where type = 3   

Demo here