MySQL: show a row that not contains a specific value

Question

I can't show in MySQL a specific row that not contains a value. First of all this is my two tables:

And this is an array with the info of both tables:

Bills table:

[0] => Array
    (
        [id] => 1
        [reference] => #001001
        [seat_id] => 101
        [client_id] => 10200
    )

[1] => Array
    (
        [id] => 2
        [reference] => #001002
        [seat_id] => 102
        [client_id] => 10400
    )

[2] => Array
    (
        [id] => 3
        [reference] => #001003
        [seat_id] => 103
        [client_id] => 10600
    )

Accounting_seats table:

[0] => Array
    (
        [0] => Array
            (
                [id] => 1
                [seat_id] => 101
                [account_id] => taxes_qty
                [value] => 0.99
                [client_id] => 10200
            )

        [1] => Array
            (
                [id] => 2
                [seat_id] => 101
                [account_id] => tax_base
                [value] => 4
                [client_id] => 10200
            )

        [2] => Array
            (
                [id] => 3
                [seat_id] => 101
                [account_id] => total
                [value] => 4.99
                [client_id] => 10200
            )

    )

[1] => Array
    (
        [0] => Array
            (
                [id] => 4
                [seat_id] => 102
                [account_id] => taxes_qty
                [value] => 2.00
                [client_id] => 10400
            )

        [1] => Array
            (
                [id] => 5
                [seat_id] => 102
                [account_id] => tax_base
                [value] => 8.00
                [client_id] => 10400
            )

        [2] => Array
            (
                [id] => 6
                [seat_id] => 102
                [account_id] => shipping_cost
                [value] => 2.00
                [client_id] => 10400
            )

        [3] => Array
            (
                [id] => 7
                [seat_id] => 102
                [account_id] => total
                [value] => 12.00
                [client_id] => 10400
            )

    )

[2] => Array
    (
        [0] => Array
            (
                [id] => 8
                [seat_id] => 103
                [account_id] => taxes_qty
                [value] => 3
                [client_id] => 10600
            )

        [1] => Array
            (
                [id] => 9
                [seat_id] => 103
                [account_id] => tax_base
                [value] => 7
                [client_id] => 10600
            )

        [2] => Array
            (
                [id] => 10
                [seat_id] => 103
                [account_id] => shipping_cost
                [value] => 3.99
                [client_id] => 10600
            )

        [3] => Array
            (
                [id] => 11
                [seat_id] => 103
                [account_id] => total
                [value] => 13.99
                [client_id] => 10600
            )

    )

The problem is that I cannot show the bill where the accounting_seats not contain the value "shipping_cost". Only show the 2 bills with shipping_cost or all bills, but I need the bill without shiipping_cost value.

SELECT reference, seat_id FROM bills WHERE seat_id IN(101,102,103) AND seat_id IN (SELECT seat_id FROM accounting_seats WHERE account != 'shipping_cost');

Tons of thanks for any help!

Answer 1

I would reommend not exists:

select reference, seat_id 
from bills b 
where 
    b.seat_id in(101,102,103) 
    and not exists (
        select 1
        from accounting_seats acs
        where acs.account = 'shipping_cost' and acs.seat_id = b.seat_id 
    )

As compared to not in, not exists usually scales better when the size of the underlying table increases (here, accounting_seats): the above query woulud take advantage of an index on accounting_seats(account, seat_id). Another upside of this approach is that not exists is safe as regard to null values in seat_id, while not in isn't.