SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Help with query

  1. #1
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help with query

    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:
    Code:
    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 ;

  2. #2
    Non-Member
    Join Date
    Aug 2009
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  3. #3
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome, thank you so much


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •