CODEWITHSUNDEEP
mysqlsql
BeetleJuice
BeetleJuice

Reputation: 40886

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

Upvotes: 0

Views: 85

Answers (6)

The Shooter
The Shooter

Reputation: 733

Try this query:

SELECT 
    tbl1.type
    ,tbl1.price
    ,count(A.expires) 
FROM tbl1 
INNER JOIN 
(
    SELECT 
         tbl2.type
         ,tbl2.expires
    FROM tbl2
    WHERE tbl2.expires > now()
) AS A
ON tbl1.type = A.type
WHERE tbl1.type = 3
GROUP BY tbl1.type

Edit

Just for your information: Although the above query works but isn't compatible with SQL Server or other SQL Products and won't work there.

Below query would work with any SQL Product which supports inner join and date functions to get the current date. Only change that would be required be to replace Now() with similar function in target sql product.

MySql appears to be linient in some aspects.

SELECT 
    tbl1.Type
    ,tbl1.Price
    ,A.ExpCount
FROM tbl1 
INNER JOIN 
(
    SELECT 
        tbl2.Type
        ,COUNT(tbl2.Expires) ExpCount
    FROM tbl2
    WHERE tbl2.expires > NOW()
    GROUP BY tbl2.Type
) AS A
ON tbl1.type = A.type
WHERE tbl1.type = 1

Upvotes: 1

IDHAMAY
IDHAMAY

Reputation: 11

SELECT T.* , (SELECT COUNT(*) FROM TBL2 WHERE TYPE = T.TYPE AND EXPIRES >= SYSDATE )  current
 FROM TBL1 T
WHERE T.TYPE = 1 ;

/* IF TBL2.EXPIRES DATA TYPE IS NOT DATE AT FIRST YOU SHOULD MAKE IT DATE *\

Upvotes: 0

BeetleJuice
BeetleJuice

Reputation: 40886

Thanks for your responses. After learning first from Giorgos that aggregate functions without a group by always return something, I just added a group by clause. Here is my selected query:

select tbl1.*, count(*) current
from tbl1 left join tbl2
on tbl1.type=tbl2.type
where tbl1.type=3 and tbl2.expires > now()
group by tbl1.type

Thanks all.

Upvotes: 0

FDavidov
FDavidov

Reputation: 3675

I would try this:

select type , count(counter) from (
select tbl1.type type,tbl2.expires counter
  from tbl1 left join tbl2
    on tbl1.type=tbl2.type
 where tbl1.type = 1
   and tbl2.expires > now()
) t
 group by type   ;

The difference is that in your query you are still returning something when there is no matching data (if(tbl2.expires > now(),1,null)).

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

You should note that all aggregate queries will return at least single row as result

Look at this example

SELECT 1
where 1=0;

The above returns empty result. But the following

SELECT SUM(1)
where 1=0;

Returns NULL as single row

Upvotes: 1

Related Questions