sql - MySql: find rows from Table A not indicated in Tables B or C -

sql - MySql: find rows from Table A not indicated in Tables B or C -

i need take set of ~5k recipes ,

remove ones have not been categorized remove ones have been batched

this should leave me list of categorized, unbatched recipes.

i'm finding hard subtract records initial set, when each set around 10k. queries have been trying utilize either slow or returning results i'm not confident in. i'm trying answers on numbers, query doesn't have particularly fast - reasonable do. these tables i'm using:

table recipes: these recipes manipulating. ids unique.

mysql> select id, title recipes limit 10; +---------+---------------------------------------------+ | id | title | +---------+---------------------------------------------+ | r162739 | chipotle steak | | r223652 | sweetness pea mash on toast | | r216897 | horchata latte | | r125550 | roasted beet , fennel salad | | r196267 | sweetness potatoes fruit | | r215630 | mini oatmeal-raisin cookies | | r219133 | pork butternut crush | | r166935 | herb-ginger bulgur | | r208872 | chocolate-macadamia dreams | | r220442 | smoked turkey salad | +---------+---------------------------------------------+

table recipe_log: each time adds recipe category, note made in log table. log table has 14k records - many of not type interested in. ids not unique - same recipe id may have many records.

mysql> select * recipe_log type='category changes' limit 10; +---------+---------------------+------------------+------+ | id | date | type | note | +---------+---------------------+------------------+------+ | r216064 | 2014-05-23 19:05:47 | category changes | null | | r216064 | 2014-05-23 19:06:11 | category changes | null | | r178159 | 2014-05-23 21:55:08 | category changes | null | | r178159 | 2014-05-24 02:13:34 | category changes | null | | r178159 | 2014-05-24 21:49:20 | category changes | null | | r178159 | 2014-05-24 21:52:52 | category changes | null | | r178613 | 2014-05-24 21:58:07 | category changes | null | | r178613 | 2014-05-24 21:59:24 | category changes | null | | r178159 | 2014-05-24 22:00:11 | category changes | null | | r178613 | 2014-05-24 22:00:51 | category changes | null | +---------+---------------------+------------------+------+

table recipe_batches: table contains recipes have been added batch. ids not unique - same recipe may added more 1 batch. there 10 records in table.

mysql> select recipe_id, batch_id, date recipe_batches limit 10; +-----------+----------+---------------------+ | recipe_id | batch_id | date | +-----------+----------+---------------------+ | r109651 | eh_1_250 | 2014-06-04 01:01:01 | | r111068 | eh_1_250 | 2014-06-04 01:01:01 | | r113500 | eh_1_250 | 2014-06-04 01:01:01 | | r117349 | eh_1_250 | 2014-06-04 01:01:01 | | r117494 | eh_1_250 | 2014-06-04 01:01:01 | | r109648 | eh_1_250 | 2014-06-04 01:01:01 | | r109652 | eh_1_250 | 2014-06-04 01:01:01 | | r110440 | eh_1_250 | 2014-06-04 01:01:01 | | r113004 | eh_1_250 | 2014-06-04 01:01:01 | | r111068 | eh_1_250 | 2014-06-04 01:01:01 | +-----------+----------+---------------------+

you can list of recipe_id's first list of 1 categorized since want maintain them , remove ones have been batched. unless understood wrong every recipe has been categorized should returned unless in batch.

select distinct a.recipe_id recipe_log a.type='category changes' , not exists (select * recipe_batches b a.recipe_id = b.recipe_id)

mysql sql join