MySQL Select all from Table 1 where Foreign key not exists or is not equal to specified key

Hello I can’t figure out how to write this select statement. I have the following schema: table 1 1:n table 2 n:1 table 3 So I got a n:m between table 1 and 3 what I want to do now is get all entries in table 1 that either don’t have an relation to table 3 via table 2 or if there exists a relation I want only those not matching one specific table 3 primary key. Or in other words if table 1 is products and table 3 is orders, I want to get out all products not already placed in the specific order I’m giving into the select. I already tried a vaiety of select commands but none of them were working properly, hardest thing for me is the ‘not equals orders_id’ part so far I only got an empty table as a result. I did a small paint drawing so you can understand better what I’m trying to do.

Answer

I would recommend not exists:

select p.*
from products p
where not exists (
    select 1
    from product_orders po
    where po.product_id = o.product_id and po.order_id = ?
)

The question mark represents the id of the order that you give as a parameter to the query.

I doubt that you need table orders to generate the result you want – unless you are identifying the order by some other column than its primary key. If so, that would be:

select p.*
from products p
where not exists (
    select 1
    from product_orders po
    inner join orders o on o.order_id = po.order_id
    where po.product_id = o.product_id and o.<somecol> = ?
)