Help with query

[adammc]

Hi guys,

Can anyone please help, I really dont know how to achieve this:

I have 2 tables (Products and Product_keywords). I want to list on a page all products that do not have an entry (keyword assigned to it in the Product_keywords table.

I assume I need to do a join somehow and then do the check based on 'product_id' but have no idea how.

Can anyone possibly help?


Database structure:

CREATE TABLE IF NOT EXISTS `Product_keywords` (
  `id` int(11) NOT NULL auto_increment,
  `product_id` int(11) NOT NULL,
  `keyword` varchar(100) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `product_id_keyword` (`keyword`,`product_id`),
  KEY `product_id` (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1038 ;



CREATE TABLE IF NOT EXISTS `Products` (
  `product_id` mediumint(8) unsigned NOT NULL auto_increment,
  `product_code` varchar(15) default NULL,
  `category_id` mediumint(3) unsigned NOT NULL default '0',
  `product_name` varchar(80) default NULL,
  `short_description` varchar(180) default NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) unsigned default '0.00',
  `new` mediumint(3) unsigned NOT NULL default '0',
  `featured` mediumint(3) unsigned NOT NULL default '0',
  `spotlight` mediumint(3) unsigned NOT NULL default '0',
  `active` mediumint(3) unsigned NOT NULL default '0',
  `date_added` datetime NOT NULL default '0000-00-00 00:00:00',

  PRIMARY KEY  (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=195 ;

[vasyapupkin]

I did not use SQL for 10 years or so, but my guess is that it should be something like
select * from Products where product_id not in (select distinct product_id from Product_keywords)

[adammc]

Awesome, thank you so much