mysql - SQL query to get expected result -

this db schema

table_a -id  -status  table_b -id  table_a_has_b -id -id_a -id_b 

i need select records table_b, associated table_a records has status=1 , if table_b record hasn't associated table_a should selected.

test case:

create table table_a (id int(2),status int(1)); insert table_a (id, status) values (1,1),(2,0),(3,1),(4,1),(5,1);  create table table_b (id int(2)); insert table_b (id) values (1),(2),(3),(4);  create table table_a_has_b (id int(2),id_a int(2),id_b int(2)); insert table_a_has_b (id, id_a, id_b) values(1, 1, 1),(2, 2, 1),(3, 3, 1),(4, 4, 2),(5, 5, 2),(6, 3, 4),(7, 4, 4); 

the query should select:

+----+ |b.id| +----+ |   2| |   3| |   4| +----+ 

• id 1 shouldn't selected because 1 of table_a records has status=0
• id 2 , 4 should selected because table_a records has status=1
• id 3 should selected because don't has table_a records associated, point of view same criterion is: id 3 should selected because don't have table_a records status=0

select ... table_b b     left join   (                 select ab.id_b, a.id, a.status                 table_a_has_b ab                     join table_a                         on a.id = ab.id_a                 a.status = 1                 ) z         on z.id_b = b.id 

this portion of original post not clear: if table_b record hasn't associated table_a should selected..

if want rows table_b then:

select b.* table_b b exists    (                 select 1                 table_a_has_b ab                     join table_a                         on a.id = ab.id_a                 a.status = 1                     , ab.id_b = b.id                 ) 

if want rows table_ b either there no rows in table_a_has_b given id_b or if there rows must associated table_a item status 1, :

select b.* table_b b exists    (                 select 1                 table_a_has_b ab                     join table_a                         on a.id = ab.id_a                 a.status = 1                     , ab.id_b = b.id                 )     or not exists   (                     select 1                     table_a_has_b ab                     ab.id_b = b.id                     ) 

if want rows table_a status 1 , others null, first query provided solution. providing expected output results tremendously.

edit given update op

given update, makes clear trying achieve, following:

select b.id table_b b not exists    (                     select 1                     table_a_has_b ab                         join table_a                             on a.id = ab.id_a                     a.status <> 1                         , ab.id_b = b.id                     )