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]
