Mysql多选用排除行

Question

Have 2 tables:

A(`A_id`,`A_name`)

and

B(`B_id`,`A_id`,`B_kind`)

B_kind column is ENUM('type 1','type 2'). So, how can I get all rows A which have an entry in table B with a B_kind='type 1' but without B_kind='type 2'.

Representation in php:

$A = array();
$B = array();
$sample = array();
foreach( $A as $key => $value )
{
    if( find_type1($value,$B) && ! find_type2($value,$B) )
    $sample[] = $A;
}

Please, help, how can I do that?

Answer 1

You could use exists and not exists:

select  *
from    A
where   exists
        (
        select  *
        from    B
        where   B.A_Id = A.A_Id
                and B.B_kind = 'type 1'
        )
        and not exists
        (
        select  *
        from    B
        where   B.A_Id = A.A_Id
                and B.B_kind = 'type 2'
        )