Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

What will be the correct MySQL join query to fetch required results?

On my websites, I have some filters to search for data. The data is served through various following 4 tables:

1. tb_contacts

enter image description here

2. tb_data_fields

enter image description here

3. tb_data_values

enter image description here

4. tb_data_cat_options

enter image description here

Now, I want to do following queries:

  • How to fetch all "Sachin" from "India"?
  • How to fetch all "Sachin" from "India" which work in "Software" industry?
  • How to fetch all "Sachin" from "Delhi"?

I am trying below query to fetch first result, but don't know how to expand this query to get more combination of result or narrow down the search using more filters:

SELECT DISTINCT c.*
              , df.field_name
              , dv.field_value 
           FROM tb_contacts c 
           JOIN tb_data_values dv 
             ON c.contact_id = dv.contact_id 
           JOIN tb_data_fields df 
             ON dv.field_id = df.field_id 
          WHERE c.contact_name = "Sachin Sharma" 
            AND dv.field_value = "India" 

Output

enter image description here

Guys, please help me on sorting out this issue.

Comments