Why Unknown column in 'having clause'-CodePudding

The following codes work well:

select session_id
from playback
where session_id not in 
    (select session_id
     from playback a join ads b
     on timestamp between start_time and end_time
     and a.customer_id = b.customer_id)

I tried to change the following part in subquery:

 join on timestamp between start_time and end_time
    and a.customer_id = b.customer_id

 join on a.customer_id = b.customer_id
 having b.timestamp between a.start_time and a.end_time

but got error as: Unknown column 'b.timestamp' in 'having clause'

What's the problem using having() here?

CodePudding user response：

The issue happens when you have Having without using Group By.

I haven't had time to test these, but here are three possible ways that you could rewrite your statements:

Without group by

select session_id
from playback
where session_id not in 
    (select distinct a.session_id
     from playback as a 
          join ads as b
     on a.customer_id = b.customer_id
     where b.timestamp between a.start_time and a.end_time
)

With group by

select session_id
from playback
where session_id not in 
    (select a.session_id
     from playback as a 
          join ads as b
     on a.customer_id = b.customer_id
     group by a.session_id
     having b.timestamp between a.start_time and a.end_time
)

By not using subquery

select distinct a.session_id
from playback as a
left join ads as b
on b.customer_id = a.customer_id
where a.timestamp not between start_time and end_time
and b.customer_id is not null

CodePudding user response：

Here is the answer that we found together on this journey:

SELECT session_id 
FROM playback pb 
LEFT JOIN ads ad 
ON pb.customer_id = ad.customer_id 
AND ad.timestamp 
BETWEEN start_time and end_time 
WHERE ad.customer_id IS NULL