Question 195961 in Mysql Question posted in
The official MySQL documentation can be found here.

Grouping mysql results by multiple key value pairs

I have a db table that stores multiple ‘tags’ for a client.

client_code tag_key tag_value
ABC price high
ABC size big
XYZ price high
XYZ size small

I need to filter for clients by particular tags.

This is easy if I am searching for one type of tag. For example, all clients that have a high price point:

SELECT DISTINCT(client_code) FROM tb_client_tags WHERE (tag_key = 'price' AND tag_value = 'high')

How would I do this for multiple key value pairs?
For example, if I wanted to search by price and size.

The following works, but it seems quite inefficient!


SELECT client_code FROM 
(
    SELECT client_code, SUM(C) AS total FROM 
    (
        SELECT DISTINCT(client_code), 1 AS C FROM tb_client_tags WHERE (tag_key = 'pricepoint' AND tag_value = 'medium')
        UNION ALL
        SELECT DISTINCT(client_code), 1 AS C FROM tb_client_tags WHERE (tag_key = 'client_size' AND tag_value = 'large')
    ) T
    GROUP BY client_code
) T_1
WHERE total = 2;

Any tips would be most appreciated!

Tags:

2

Answers


  1. 0

    You can search clients that match either of the condition (WHERE) and then choose those that match all of them (HAVING):

    SELECT client_code
FROM tb_client_tags
WHERE 
  (tag_key='price' and tag_value='high') or 
  (tag_key='size' and tag_value='big')
GROUP BY client_code
HAVING COUNT(DISTINCT tag_key)=2

    Btw, in your sample code DISTINCT is not a function. It affects all columns.

    Login or Signup to reply.
  2. 0

    If you need to filter by two key-value pairs only then

    SELECT client_code
FROM tb_client_tags t1
JOIN tb_client_tags t2 USING (client_code)
WHERE (t1.tag_key, t1.tag_value) = ('price', 'high')
  AND (t2.tag_key, t2.tag_value) = ('size' , 'big' )

    if you need to filter by 2-3-4-more key-value pairs then use generic technique provided in @slaakso‘s answer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search