Fetching rows based on another row from the same table

Hi,

I don't know how to explain in words, but I believe it's best to explain my doubt using examples.

Say, my table name is "tblSettings" and some dummy rows are like this:

Code:

---

rec_id        | settings_name        | settings_value        | reference_id
---------------------------------------------------------------
1                | country                | India                                | 32
2                | state                        | Kerala                        | 32
3                | country                | India                                | 40
4                | state                        | Tamil Nadu                | 40
5                | country                | America                        | 61
6                | state                        | Los Angles                | 61

---

Consider this as my scenario. Btw, discard the table design. It's not designed by me.
So, I want to fetch all DISTINCT "state" values that belongs to the "country" named "India"

In the table above, there are more than 65000 number of rows in total.

Any idea on how I could I implement the query? I can't think straight now.

:wave:

I think I got it:

Code:

---

SELECT DISTINCT(`settings_value`) AS `values` FROM `tblSettings` WHERE `settings_name` = 'state' AND `reference_id` IN( SELECT `reference_id` FROM `tblSettings` WHERE `settings_name` = 'country' AND `settings_value` = 'India' )

---

:wave: