The reason ur getting empty result set is becuase both tables have raw_mat_id (1,2). Insert a new record into raw_materials and not into raw_materials_product, then you would get the missing record.
On 12/3/08, Nigel Peck <[EMAIL PROTECTED]> wrote: > > Nigel Peck wrote: > >> Ananda Kumar wrote: >> >>> did u try this >>> select * from raw_materials where raw_mat_id not in (select raw_mat_id >>> from >>> raw_materials__Products); >>> >> >> Hi Ananda, >> >> Thanks for this, I'm really looking to do this in a single SELECT, for >> efficiency. I'm sure it's possible with a join but just not sure how. >> >> I've just tried: >> >> SELECT `Raw__materials`.`raw_mat_id`, `Raw__materials`.`name` >> FROM >> `Raw__materials` >> LEFT JOIN >> `Raw__materials__Products` >> ON >> `Raw__materials`.`raw_mat_id` = >> `Raw__materials__Products`.`raw_mat_id` >> WHERE >> `Raw__materials__Products`.`stock_id` IS NULL AND >> `Raw__materials__Products`.`product_id` = '1' >> >> But I just get an empty result set. Although the manual seems to suggest >> that it should work. >> >> I tried taking out " AND `Raw__materials__Products`.`product_id` = '1'" >> but still an empty result set. >> >> Cheers, >> Nigel >> >> > Sorry, make that: > > SELECT `Raw_materials`.`raw_mat_id`, `Raw_materials`.`name` > FROM > `Raw_materials` > LEFT JOIN > `Raw_materials__Products` > ON > `Raw_materials`.`raw_mat_id` = > `Raw_materials__Products`.`raw_mat_id` > WHERE > `Raw_materials__Products`.`raw_mat_id` IS NULL AND > `Raw_materials__Products`.`product_id` = '1' > > Cheers, > Nigel > >
