did u try this select * from raw_materials where raw_mat_id not in (select raw_mat_id from raw_materials__Products);
On 12/3/08, Nigel Peck <[EMAIL PROTECTED]> wrote: > > Hi all, > > I'm having trouble figuring out how to construct a query and am hoping > someone can help... > > I have three tables: > > Raw_materials > +------------+------------------------+-------+ > | raw_mat_id | name | count | > +------------+------------------------+-------+ > | 1 | Small Organza Bag | 10 | > | 2 | Lavender Incense Cones | 10 | > +------------+------------------------+-------+ > > Raw_materials__Products > +------------+------------+ > | raw_mat_id | product_id | > +------------+------------+ > | 1 | 2 | > | 2 | 2 | > | 2 | 1 | > +------------+------------+ > > Products > +------------+-----------------------------+-------+-------+ > | product_id | name | price | rrp | > +------------+-----------------------------+-------+-------+ > | 1 | Large Salt Lamp | 59.99 | 69.99 | > | 2 | Lavender Incense Cones (15) | 3.99 | 0.00 | > +------------+-----------------------------+-------+-------+ > > CREATE TABLE `Raw_materials__Products` ( > `raw_mat_id` int(10) unsigned NOT NULL, > `product_id` int(10) unsigned NOT NULL, > PRIMARY KEY (`raw_mat_id`,`product_id`), > KEY `product_id` (`product_id`), > CONSTRAINT `Raw_materials__Products:raw_mat_id` FOREIGN KEY (`raw_mat_id`) > REFERENCES `Raw_materials` (`raw_mat_id`) ON UPDATE CASCADE, > CONSTRAINT `Raw_materials__Products:product_id` FOREIGN KEY (`product_id`) > REFERENCES `Products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | > > Knowing a product_id, I want to select all of the Raw_materials rows that > do NOT already have a relationship in Raw_materials__Products. > > So for product_id "1" I would get raw_mat_id "1" and for product_id "2" I > would get an empty result set. > > I tried various queries but can't get anywhere, please help. > > Thanks in advance, > Nigel > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
