I have a problem with my mysql query. I have two tables:
"data" table:
| id | name |
|---|---|
| 1 | one |
| 2 | two |
and "fields" table:
| id | data_id | type | value |
|---|---|---|---|
| 1 | 1 | 1 | foostring |
| 2 | 1 | 2 | barstring |
| 3 | 1 | 3 | string |
| 4 | 2 | 1 | foobarstring |
| 5 | 2 | 2 | string |
| 6 | 2 | 3 | string |
I want to get all "data" table rows that id is related to "fields" table where in "value" column are phrases "foo" and "bar". Both for one or more "type" values. So I need to merge "value" column from all "type" rows and then search for phrases. My query below searches for both phrases in one row then concat the rows but I want the opposite. First merge then search.
SELECT `data`.*, GROUP_CONCAT(`f`.`value`) AS 'value' FROM `data`
LEFT JOIN `fields` AS `f` ON `f`.`data_id` = `data`.`id` AND `f`.`type` IN ('1','2','3')
WHERE `value` LIKE '%foo%' AND `value` LIKE '