# Count sql failing with unknown table

**URL:** <https://www.ruby-forum.com/t/count-sql-failing-with-unknown-table/50042>\
**Category:** Rails\
**Created:** [December 12, 2005, 2:36pm UTC](https://www.ruby-forum.com/t/count-sql-failing-with-unknown-table/50042 "2005-12-12T14:36:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![weliwarmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/a587f6/32.png) [@weliwarmer](https://www.ruby-forum.com/u/weliwarmer)\
**Post date:** [December 12, 2005, 2:36pm UTC](https://www.ruby-forum.com/t/count-sql-failing-with-unknown-table/50042/1 "2005-12-12T14:36:15Z")

</div>

Hey Rails People,

I’m doing my first Rails app and I’m having a newbie problem with the  
count method…

Mysql::Error: #42S02Unknown table ‘jobs\_sectors’ in where clause: SELECT  
COUNT(\*) FROM jobs WHERE (jobs\_sectors.sector\_id = 1 )

Extracted source (around line #4):

1:

  
2: 
  
3: \<% for sector in @sectors -%\>  
4: - \<%= sector.name %\> ( \<%= sector.jobs.count %\> )
  
5: \<% end -%\>  
6: 
  
7: 

My test application has jobs with multiple sectors, see below for DDL.  
The query generated by sector.jobs.count seems a bit funny. I’m no  
MySQL expert but should there be more references to the join between  
jobs and jobs\_sectors?

Thanks for you time,  
Tim

DDL as promised…

CREATE TABLE `jobs_sectors` (  
`job_id` mediumint(9) NOT NULL default ‘0’,  
`sector_id` mediumint(9) NOT NULL default ‘0’  
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `jobs` (  
`id` int(10) unsigned NOT NULL auto\_increment,  
`title` varchar(254) NOT NULL default ‘’,  
`description` text NOT NULL,  
`start_sallary` float NOT NULL default ‘0’,  
`end_sallery` float NOT NULL default ‘0’,  
`created_at` datetime NOT NULL default ‘0000-00-00 00:00:00’,  
`valid_on` datetime default NULL,  
`invalid_on` datetime default NULL,  
`location_id` mediumint(9) NOT NULL default ‘0’,  
`user_id` mediumint(9) NOT NULL default ‘0’,  
`type_id` mediumint(9) NOT NULL default ‘0’,  
PRIMARY KEY (`id`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `sectors` (  
`id` int(10) unsigned NOT NULL auto\_increment,  
`name` varchar(50) NOT NULL default ‘’,  
PRIMARY KEY (`id`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

---

<div class="post-metadata">

**Author:** ![weliwarmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/a587f6/32.png) [@weliwarmer](https://www.ruby-forum.com/u/weliwarmer)\
**Post date:** [December 12, 2005, 3:08pm UTC](https://www.ruby-forum.com/t/count-sql-failing-with-unknown-table/50042/2 "2005-12-12T15:08:50Z")

</div>

How is your model defined? Maybe there is something there…

2005/12/12, Tim S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> MySQL expert but should there be more references to the join between  
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;  
> `location_id` mediumint(9) NOT NULL default ‘0’,
> 
> –  
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

# –

# Eduardo Leao Garcia removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![weliwarmer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/a587f6/32.png) [@weliwarmer](https://www.ruby-forum.com/u/weliwarmer)\
**Post date:** [December 12, 2005, 3:16pm UTC](https://www.ruby-forum.com/t/count-sql-failing-with-unknown-table/50042/3 "2005-12-12T15:16:56Z")

</div>

Garcia?= wrote:

> How is your model defined? Maybe there is something there…

Here’s the models…

class Job \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :sectors  
belongs\_to :area  
belongs\_to :user  
belongs\_to :type  
end

class Sector \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :jobs  
end

not much from the default.

My versions are

# ruby -v

ruby 1.8.4 (2005-10-29) [i486-linux]

# rails -v

Rails 0.14.4

# gem -v

0.8.11

# mysql --version

mysql Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i386) using readline  
4.3