mysql unknown column

Question

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

Jonathan Leffler · Accepted Answer · 2010-10-13 00:56:25Z

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';

answered Oct 13, 2010 at 0:56

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

OMG Ponies Over a year ago

+1: You beat me; I was working on an example using EXISTS. The JOIN to action_6_members risks inflating records if there's more than one child associated to a parent record.

Jonathan Leffler Over a year ago

@OMG: but the DISTINCT undoes any of that damage, does it not? Also, I think the 'Action 6' people are on a special (mobile phone) rate plan - and this is counting the number of calls sent between the two dates to people on the Action 6 plan - so there will only be one number (at most) in the Action 6 table for any given 'to_number'.

Hailwood Over a year ago

hmm, dont really like joins but ok :) Just incase, this query should basically select all unique mobile(to_number) from the sent_txts table(based on the where) but only count it if the number exists in the action_6_members, can you see a better way?

Hailwood Over a year ago

hmm, trying the query above locks up my sql client. (could it be that it is a 30k row table joining a 10k row table?)

Jonathan Leffler Over a year ago

@Hailwood: There's a very decent chance that the join will be more efficient than the IN sub-query (unless the optimizer is smart enough to convert the IN into a join of its own accord), unless the number of people in the Action 6 table is very small (say, under 100). And joins are usually effective - they are, after all, the bread and butter of an optimizer. If your DBMS can't manage joins efficiently, get a better DBMS (meaning one that can manage joins efficiently).

|

Collectives™ on Stack Overflow

mysql unknown column

1 Answer 1

18 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Related