Mysql multi select with excluding row

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'
        )

Answer 2

SELECT *
FROM A
JOIN B ON (A.A_id = B.A_id AND B.B_kind = 'type 1')

Answer 3

I take it you want to exclude rows from A which have both an A_id of 'type 2' and an A_id of 'type 1' in B?

SELECT A.*
FROM A
INNER JOIN B as B1 ON (B1.A_id = A.A_id AND B1.B_kind = 'type 1')
LEFT JOIN B as B2 ON (B2.A_id = A.A_id AND B2.B_kind = 'type 2')
WHERE B2.A_id IS NULL

The 'IS NULL' in the WHERE clause ensures that only rows that don't match the 'LEFT JOIN' are included.