Get products for a specific city?

Question

MySQL

Hello!

there is a table, product_cities
fields:
-product_id (relationship with a product from the products table)
-city_id (relationship with a city from the cities table)
-can_be_sold (can be sold only for this city, not for other cities)
-can_not_be_sold (not for sale for this city , for other cities it is sold)

When selecting, three conditions must be taken into account:

1. if there are no records for this product in this table, then we display
2. if there is a record for this product and city_id and can_be_sold = 1, then we display
3. if there is a record for this goods and city_id BUT can_not_be_sold = 1 then we DO NOT DISPLAY ALREADY.

Here I cannot consider all three conditions in one SQL request in any way. Any ideas?

Answer 1

Solution, maybe it will help someone
In my case, the names of the tables are different from those in the question.
------

SELECT
  master_products.id,master_products.name,master_product_regions.master_product_id AS mpr_id 				
FROM
  master_products

JOIN master_product_categories  on master_products.id=master_product_categories.master_product_id
      
 LEFT JOIN  master_product_regions    ON  master_products.id=master_product_regions.master_product_id
 WHERE
    master_product_categories.category_id=803
  AND 
      (  
         master_product_regions.id is NULL 
         OR (master_product_regions.city_id = ".$city." AND master_product_regions.can_be_sold = 1)
         OR (master_product_regions.city_id != ".$city." AND master_product_regions.can_not_be_sold=1)
            )