CODEWITHSUNDEEP
mysqlsqljoinwhere-clauseinverse
London Smith
London Smith

Reputation: 1659

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".

Upvotes: 0

Views: 114

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

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");

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could try using left join but for nkt matching rows

SELECT distnct sh__shop.id AS shop_id
FROM sh__shop 
LEFT JOIN sh__shop_invoice ON sh__shop_invoice.shop_id = sh__shop.id
    AND  sh__shop_invoice.month_commission LIKE "2021-08-01"
WHERE sh__shop_invoice.shop_id is null

(In your query you are using left join in wrong way because adding the left joined column in where clause this work as an inner join)

Upvotes: 1

Related Questions