mysql unknown column...

Question

Hi there

I am using this query

SELECT COUNT(DISTINCT to_number) AS errors FROM sent_txts
WHERE msg_link_id = 0 AND 
msg_sent_datetime BETWEEN '2010-08-09 07:00:00' AND '2010-09-07 11:59:59'
HAVING to_number IN(SELECT mobile FROM action_6_members WHERE mobile = to_number)

However I am getting

Unknown column 'tada_prod.sent_txts.to_number' in 'where clause'

If I comment out the having the query runs fine.

Here is the sent_txts table

CREATE TABLE `sent_txts` (
  `id` int(64) NOT NULL AUTO_INCREMENT,
  `msg_link_id` int(64) DEFAULT NULL,
  `msg_class` varchar(256) DEFAULT NULL,
  `msg_ref` varchar(256) DEFAULT NULL,
  `to_number` varchar(256) DEFAULT NULL,
  `msg_body` text,
  `waiting_for_reply` int(64) DEFAULT NULL,
  `status` varchar(256) DEFAULT NULL,
  `tada_error` int(64) DEFAULT NULL,
  `msg_sent_datetime` datetime DEFAULT NULL,
  `reply_type` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=256379 DEFAULT CHARSET=utf8

So obviously the column is there.

What is the issue?

---

Here is the explain of the query posted by Jonathan

id  select_type  table  type  possible_keys  key  key_len  ref  rows    Extra                           
1   SIMPLE       a      ALL                                     10895                                   
1   SIMPLE       s      ALL                                     256050  Using where; Using join buffer  

Here is the show create for action_6_members:

CREATE TABLE `action_6_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `draw_id` int(11) NOT NULL,
  `mobile` varchar(255) NOT NULL,
  `fly_buys` varchar(255) NOT NULL,
  `signup_date` datetime NOT NULL,
  `club` int(11) NOT NULL DEFAULT '0' COMMENT '1 = yes, 2 = no',
  `code` varchar(7) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10896 DEFAULT CHARSET=latin1

Answer 1

Normally, a HAVING clause relates an aggregate to a value or another aggregate. What is in the HAVING clause of the question should be in the main WHERE clause:

SELECT COUNT(DISTINCT to_number) AS errors
  FROM sent_txts AS s
 WHERE msg_link_id = 0
   AND msg_sent_datetime BETWEEN '2010-08-09 07:00:00'
                             AND '2010-09-07 11:59:59'
   AND to_number IN (SELECT mobile FROM action_6_members AS a
                      WHERE a.mobile = s.to_number)

But you should probably make that into a regular join:

SELECT COUNT(DISTINCT s.to_number) AS errors
  FROM sent_txts AS s
  JOIN action_6_members AS a ON a.mobile = s.to_number
 WHERE s.msg_link_id = 0
   AND s.msg_sent_datetime BETWEEN '2010-08-09 07:00:00'
                               AND '2010-09-07 11:59:59';