CODEWITHSUNDEEP
mysql
Sachin
Sachin

Reputation: 1698

How to search multiple values in same column in MySQL JOIN?

I have tried my best to explain my problem. Please go through my question carefully to understand what I want to achieve.

I am trying to search multiple values in same column in MySQL. I have following 4 tables employed in this operations which are:

contacts:

enter image description here

data_values:

enter image description here

data_fields:

enter image description here

data_cats_options:

enter image description here

On my website front-end, the filters (form elements to search for required data) look as:

enter image description here

enter image description here

Single drop-down (country) search:

To fetch all contacts with name "Sachin" whose "Country" is "India", I use following query:

SELECT `c`.* 
FROM `contacts` AS `c` JOIN `data_values` AS `dv` 
ON `c`.`contact_id` = `dv`.`contact_id` 
JOIN `data_fields` AS `df` ON 
`dv`.`field_id` = `df`.`field_id` 
JOIN `data_cats_options` AS `dco` ON 
`dv`.`field_val` = `dco`.`val_id`  
WHERE `c`.`contact_name` = "Sachin" AND `dv`.`field_id` = 153 AND `dco`.`val_id` = 224

Similarly, we can do for all "Sachin" who are accountant for "Job" dropdown.

PROBLEM

I really can't figure out how to search for multiple data e.g. How to fetch all contacts with "Sachin" whose "Country" is "India" AND "Job" is "Accountant". The following SQL gives me no result though the data exists in the database:

SELECT `c`.* 
FROM `contacts` AS `c` JOIN `data_values` AS `dv` 
ON `c`.`contact_id` = `dv`.`contact_id` 
JOIN `data_fields` AS `df` ON 
`dv`.`field_id` = `df`.`field_id` 
JOIN `data_cats_options` AS `dco` ON 
`dv`.`field_val` = `dco`.`val_id`  
WHERE `c`.`contact_name` = "Sachin" AND `dv`.`field_id` = 153 AND `dco`.`val_id` = 224 
AND `dv`.`field_id` = 154 AND `dco`.`val_id` = 227

My main concern is all about this particular segment of above query:

AND `dv`.`field_id` = 153 AND `dco`.`val_id` = 224 
AND `dv`.`field_id` = 154 AND `dco`.`val_id` = 227

So how can we search the multiple data in same columns in this JOIN?

I really want to fetch all those records which fulfills both dropdown options.

Please help me on this. Thanks in advance.

Upvotes: 1

Views: 58

Answers (1)

xQbert
xQbert

Reputation: 35333

This seems like a bad design because we have to add additional joins.

SELECT c.* 
FROM contacts AS c 
JOIN data_values AS dv 
  ON c.contact_id = dv.contact_id 
JOIN data_fields AS df 
  ON dv.field_id = df.field_id 
JOIN data_cats_options AS dco 
  ON dv.field_val = dco.val_id 
JOIN data_values AS dv2 
  ON c.contact_id = dv2.contact_id 
JOIN data_fields AS df2 
  ON dv2.field_id = df2.field_id 
JOIN data_cats_options AS dco2 
  ON dv2.field_val = dco2.val_id 
WHERE c.contact_name = "Sachin" 
  AND dv.field_id = 153 AND dco.val_id = 224 
  AND dv2.field_id = 154 AND dco2.val_id = 227

I'm still considering other options but need need to do something else for a bit.

Perhaps... the 2 would be dynamically set based on the number of field_ID's being searched on ; which you know since you have the field_IDs...

SELECT c.contact_ID, C.Contact_name
FROM contacts AS c 
JOIN data_values AS dv 
  ON c.contact_id = dv.contact_id 
JOIN data_fields AS df 
  ON dv.field_id = df.field_id 
JOIN data_cats_options AS dco 
  ON dv.field_val = dco.val_id 
WHERE c.contact_name = "Sachin" 
  AND (dv.field_id = 153 AND dco.val_id = 224 
  OR dv.field_id = 154 AND dco.val_id = 227)
GROUP BY c.contact_ID, C.Contact_name
HAVING count(*) = 2

Why this works? Because we know that if both matches occur, we will get 2 records per contact_ID if both don't exist we would only get 1 record. thus the having lets us limit the desired way.

Or we could use HAVING COUNT() = (Select count() from data_Fields where field_ID in ('153','154'))

Upvotes: 1

Related Questions