CODEWITHSUNDEEP
mysql
Kevin Howard Goldberg
Kevin Howard Goldberg

Reputation: 349

MySQL select one field from an Attributes table WHERE condition is in multiple rows

I started here: MySQL select one field from table WHERE condition is in multiple rows

This works fine - thank you!

The additional complexity is that I need to search within multiple attributes in a single search.

Here's a data snapshot. The attribute_ids are:

1  - language
18 - phone1
19 - phone2
20 - phone3

Sample data

+-----+------------+--------------+------------------------+
| id  | contact_id | attribute_id | stored_attribute_value |
+-----+------------+--------------+------------------------+
|  15 |          1 |            1 | english                |
|  83 |          5 |            1 | english                |
| 153 |          9 |            1 | english                |
| 197 |         11 |            1 | english                |
| 250 |          3 |            1 | english                |
| 267 |         13 |            1 | tagalog                |
| 303 |         15 |            1 | spanish                |
| 374 |         19 |            1 | spanish                |
| 469 |         17 |            1 | spanish                |
| 490 |         21 |            1 | spanish                |
| 507 |          7 |            1 | english                |
|   9 |          1 |           18 | 983-296-3660           |
|  77 |          5 |           18 | 123-300-3985           |
| 147 |          9 |           18 | 215-857-7105           |
| 191 |         11 |           18 | 123-216-8501           |
| 244 |          3 |           18 | 478-786-4450           |
| 261 |         13 |           18 | 802-118-7211           |
| 297 |         15 |           18 | 998-370-4612           |
| 367 |         19 |           18 | 203-435-4023           |
| 463 |         17 |           18 | 945-519-5355           |
| 481 |         21 |           18 | 425-675-8912           |
| 501 |          7 |           18 | 123-712-6946           |
|  11 |          1 |           19 | 123-653-3722           |
|  79 |          5 |           19 | 396-609-5772           |
| 149 |          9 |           19 | 261-899-1470           |
| 193 |         11 |           19 | 673-452-9545           |
| 246 |          3 |           19 | 760-700-5826           |
| 263 |         13 |           19 | 123-701-7931           |
| 299 |         15 |           19 | 123-445-5874           |
| 369 |         19 |           19 | 711-657-8183           |
| 465 |         17 |           19 | 123-130-2816           |
| 483 |         21 |           19 | 123-391-1234           |
| 503 |          7 |           19 | 123-568-1263           |
| 485 |         21 |           20 | 123-428-6610           |
+-----+------------+--------------+------------------------+

So if I were to search for all contacts with language 'english' and phone1 like '123%', the query would be:

SELECT `contact_id` 
FROM (`contact_attribute_value`) 
WHERE (`attribute_id` = '18' AND `stored_attribute_value` LIKE '123%') 
OR (`attribute_id` = '1' AND `stored_attribute_value` = 'english') 
GROUP BY `contact_id` HAVING COUNT(*) = 2

And I would get 3 results returned: 5, 7, and 11 which is correct.

The challenge is that I want to create a generic phone field in the search interface so that if a user searches for a phone number, they search all three phone fields simultaneously.

So, I wrote the following query:

SELECT `contact_id` 
FROM (`contact_attribute_value`) 
WHERE (`attribute_id` = '18' AND `stored_attribute_value` LIKE '123%') 
OR (`attribute_id` = '19' AND `stored_attribute_value` LIKE '123%') 
OR (`attribute_id` = '20' AND `stored_attribute_value` LIKE '123%')
OR (`attribute_id` = '1' AND `stored_attribute_value` = 'english') 
GROUP BY `contact_id` HAVING COUNT(*) = 2

Conceptually that works, but there are conditions in which it breaks.

The first condition is when a contact has language of 'english' and two phone numbers that match like '123%'. Here the contact gets a count of 3 and does not show up in the results.

The second condition is when a contact has a language not equal to 'english' and also has two phone numbers that match like '123%'. In this case, the contact gets a count of 2 and shows up in the results, but that's not what is desired.

I'm sure there's a "hard coded" way of trapping for these conditions in this scenario, but the set of attributes and possible searches is quite large so I need a generalizable solution.

Thanks in advance!

Upvotes: 2

Views: 2734

Answers (2)

asermax
asermax

Reputation: 3123

Just taking into account your example and the conditions you explained, I would say that the easiest way to solve this without doing two queries, is with a subquery: 

SELECT DISTINCT contact_id
FROM contact_attribute_value AS c
WHERE c.attribute_id IN (18, 19, 20)
AND c.stored_attribute_value LIKE '123%'
AND EXISTS (SELECT * 
            FROM contact_attribute_value AS c1 
            WHERE c1.contact_id = c.contact_id  AND c1.attribute_id = 1 
            AND c1.stored_attribute_value = 'english')

With this query, first we check if the contact haves any number that starts with 123, and then the subquery takes care of checking if the contact haves english as his language.

The DISTINCT keyword removes dupliciates, so no need for grouping anymore.

Upvotes: 3

peterm
peterm

Reputation: 92785

If I understand you correctly try

SELECT c1.contact_id
  FROM contact_attribute_value c1 LEFT JOIN contact_attribute_value c2
    ON c1.contact_id = c2.contact_id  
   AND c2.attribute_id = '18' LEFT JOIN contact_attribute_value c3
    ON c1.contact_id = c3.contact_id  
   AND c3.attribute_id = '19' LEFT JOIN contact_attribute_value c4
    ON c1.contact_id = c4.contact_id  
   AND c4.attribute_id = '20'
 WHERE c1.attribute_id = '1' AND c1.stored_attribute_value = 'english'
   AND (c2.stored_attribute_value LIKE '123%'
    OR  c3.stored_attribute_value LIKE '123%'
    OR  c4.stored_attribute_value LIKE '123%')

SQLFiddle

UPDATE Improved version with HAVING that uses conditional count

SELECT `contact_id`
  FROM `contact_attribute_value`
 GROUP BY `contact_id` 
HAVING SUM(CASE  WHEN `attribute_id` = '1'  
                  AND `stored_attribute_value` = 'english' THEN 1 ELSE 0 END) = 1
   AND (SUM(CASE WHEN `attribute_id` = '18' 
                  AND `stored_attribute_value` LIKE '123%' THEN 1 ELSE 0 END) 
       +SUM(CASE WHEN `attribute_id` = '19' 
                  AND `stored_attribute_value` LIKE '123%' THEN 1 ELSE 0 END) 
       +SUM(CASE WHEN `attribute_id` = '20' 
                  AND `stored_attribute_value` LIKE '123%' THEN 1 ELSE 0 END)) > 0 
;

SQLFiddle

Upvotes: 1

Related Questions