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.
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 ?
You can use
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)