Mysql filter (AND) query on multiple fields in multiple joined rows

Question

I have created a query which returns a list of products with their fields and values. Now I want to search through fields for a certain value and get a resultlist matching this search query. The problem is that I want an AND construction, so fieldx value must be like %car% and fieldy value must be like %chrome%.

Here`s an example of my query and the resultset:

Query

SELECT p.id as product,pf.field_name,pfv.field_value
FROM product p
JOIN field pf ON pf.product_id = p.product_id
JOIN field_val pfv ON pfv.field_id = pf.field_id

Resultset

product | field_name | field_value
pr1     | meta_title | Example text
pr1     | meta_kw    | keyword,keyword1
pr1     | prod_name  | Product 1
pr2 ....

So with the above query and resultset in mind I want to do the following: Query all products where meta_title contains 'Example' and where prod_name contains 'Product'. After that, I want to group the results so that only products are returned where both search queries matches.

I tried everything I could think off and I have tried many solutions on kind of the same questions, but I think mine is different because I need the AND match on the field name as well the value over multiple rows.

For example, I tried adding this as WHERE clause:

WHERE
(field_name = 'meta_title' AND field_value LIKE '%Example%') AND 
(field_name = 'prod_name' AND field_value LIKE '%Product%')

Obviously this won`t work because after the first where on meta_title there is no result left for other field names. But changing the WHERE to OR would not give me the desired result.

I also tried with HAVING but seems like same result.

Anyone an idea how to solve this, or is this just not possible?

score 0 · Answer 1 · answered Jul 08 '15 at 10:27

0

Try this:

select product from [table_name]
    WHERE field_name like 'meta_title' AND field_value LIKE '%Example%'

UNION

select product from [table_name]
    WHERE field_name like 'prod_name' AND field_value LIKE '%Product%'

answered Jul 08 '15 at 10:27

Prerak Sola

9,517
7
36
67

Thanks, I did actually think of this earlier but the reason I did not want to use this is because the query is now very small but will eventually get much bigger since the search query can extend to like 15 fields. It would be an ugly big query with many unions then. Thanks anyway! – user1281146 Jul 08 '15 at 14:21

score 0 · Accepted Answer · answered Jul 08 '15 at 10:42

A relatively simple way to do this is to use group by and having:

SELECT p.id as product
FROM product p JOIN
     field pf
     ON pf.product_id = p.product_id JOIN
     field_val pfv
     ON pfv.field_id = pf.field_id
WHERE (field_name = 'meta_title' AND field_value LIKE '%Example%') OR 
      (field_name = 'prod_name' AND field_value LIKE '%Product%')
GROUP BY p.id
HAVING COUNT(DISTINCT field_name) = 2;

By modifying the HAVING clause (and perhaps removing the WHERE), it is possible to express lots of different logic for the presence and absence of different fields. For instance > 0 would be OR, and = 1 would be one value or the other, but not both.

Perfect! Works like a charm and makes totally sense. I did not think of the combination Having with Where, which actually makes it act as an AND statement. Thanks! — user1281146, Jul 08 '15 at 14:18

score 0 · Answer 3 · edited May 23 '17 at 12:29

0

First: I would go for the easiest way. If Prerak Solas solution works and the performance is OK for you, go for it.

An other solution is to use a pivot table. With this the select of your table would return something like this:

product | meta_title   | meta_kw          | prod_name
pr1     | Example text | keyword,keyword1 | Product 1
pr2 ...

You could than easily query like:

SELECT 
  p.Id 
FROM 
  (subquery/view) 
WHERE 
  meta_title LIKE '%Example%' AND 
  prod_name LIKE '%Product%'

For more infos about pivot read this

edited May 23 '17 at 12:29

Community

1
1

answered Jul 08 '15 at 11:03

Andre

1,044
1
11
23

Thanks but I used Gordon`s solution, I will maybe test performance difference between the suggested solutions but for now the Having solution is most easy to implement for me. – user1281146 Jul 08 '15 at 14:18

Mysql filter (AND) query on multiple fields in multiple joined rows

3 Answers3

Linked