Search code examples
mysqlsqljoinwhere-clauseinverse

SELECT, get the invert result from the WHERE on the JOIN

I have this MySQL request to get all the shops having an invoice on a specific date:

SELECT sh__shop.id AS shop_id, sh__shop.name,
       sh__shop_invoice.*
FROM sh__shop 
LEFT JOIN sh__shop_invoice ON sh__shop_invoice.shop_id = sh__shop.id
WHERE sh__shop_invoice.month_commission LIKE "2021-08-01"

But I can't see how to get the inverse. All the shops not having an invoice on the specific date. I can't do NOT LIKE "2021-08-01".

Solution

  • You can make it by using not exists.

    SELECT sh__shop.id AS shop_id, sh__shop.name
FROM sh__shop 
WHERE 
    NOT EXISTS (SELECT * FROM sh__shop_invoice i
            WHERE i.shop_id = sh__shop.id
                  AND i.month_commission LIKE "2021-08-01");