banri16 banri16 - 4 months ago 7
MySQL Question

Querying A table including 0 value my sql

I need to query a location based hierarchy data. But I am having trouble on how to do the approach for querying the data. Here is a sample data for my database.

enter image description here

Now, I want to query a product based from location filter. so if I am going to select a product with a country_id of 7 and a state of 1. It will fetch all countries with a value of 7 and all state with a value of 1 but it will also include all state with an id of 0 since it not assigned on any state. How can I achieve this ?

Answer

You can use or:

where country_id = 7 and
      (state = 1 or state = 0)

Normally, I would write this as:

where country_id = 7 and state in (0, 1)