Select from one table based on value of field in another table?

Databases
1

Companies Table

CREATE TABLE IF NOT EXISTS `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` mediumtext NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `fax` varchar(50) NOT NULL,
  `website` varchar(255) NOT NULL,
  `users_allowed` int(11) NOT NULL,
  `show_ideas` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
);

Ideas Table

CREATE TABLE IF NOT EXISTS `ideas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `location_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `submitted_by` int(11) NOT NULL,
  `assigned_to` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `priority` varchar(10) NOT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'open',
  `created` date NOT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

I want to select all ideas from the ideas table but only if companies.show_ideas is set to 1.

Appreciate any help :slight_smile:

2

Use an INNER JOIN

3

companies.id = ideas.company_id
:slight_smile:

4

I thought an INNER JOIN requires that both tables contain the same field? This is not the case in my example. Both have an ID field but they do not relate to each other.