Select with multi conditions for same column and order by

Question

I have table like this

id | item_id | item_key | item_value
------------------------------------
 1 |    5    |  rating  |    6.4
 2 |    5    |  country |    US
 3 |    6    |  rating  |    7.4
 4 |    6    |  country |    UK
 5 |    7    |  rating  |    6.5
 6 |    7    |  country |    US
 7 |    8    |  rating  |    7.9
 8 |    8    |  country |    UK
 9 |    5    |  genre   |    Action
 10|    5    |  year    |    2019
 11|    6    |  genre   |    Drama
 12|    7    |  lang    |    EN

I want select item_id where have country = US and rating not empty and order by rating

EDITED : and ability to check another item_key's values like "genre = 'drama' and year = '2019'" etc

i have more than 10 item_key's variables not only rating and country but if know the method i'll apply for the others

I'm make like this and it's work but my problem with order by rating

    $sql = "SELECT metas.* FROM metas ";
    if ($cat)
    $sql .= "LEFT JOIN articles ON metas.item_id = articles.id ";
    $sql .= "GROUP BY metas.item_id HAVING (sum(metas.meta_key = 'rating' AND metas.meta_value != '' AND metas.item_type = '1') AND ";
    foreach ($filters as $filter_key => $filter_val){
        if (!empty($filter_val)){
            if (array_key_exists($filter_val, ${'m_'.$filter_key.'s'})){

                if ($filter_key == "genre")
                $sql .= (($counter == 1) ? "" : "AND ")."sum(".($cat ? "articles.category = '".$cat."' AND " : "") ."metas.meta_key = '".$filter_key."' AND metas.meta_value LIKE '%".$filter_val."%') ";
                else
                $sql .= (($counter == 1) ? "" : "AND ")."sum(".($cat ? "articles.category = '".$cat."' AND " : "") ."metas.meta_key = '".$filter_key."' AND metas.meta_value = '".$filter_val."') ";
                $trusted_filters[$filter_key] = $filter_val;
                $counter++;
            } else {
                $errs++;
            }
        }
    }
    $sql .= ") ORDER BY FIELD(metas.meta_key, 'rating') DESC, metas.meta_value DESC";

Like I say, it's work but order by not working, I need sort it by item_value based on item_key with rating value.

Please see: [Correct way to use LIKE '%{$var}%' with prepared statements?](https://stackoverflow.com/questions/28385145/correct-way-to-use-like-var-with-prepared-statements-mysqli) — Dharman, Dec 26 '19 at 22:57

mickmackusa · Accepted Answer · 2019-12-27T00:49:06.140

You don't need to JOIN the table to itself for every condition that you wish to add. You are looking for a PIVOT.

Schema (MySQL v5.7)

CREATE TABLE metas ( 
  `id` INT(11)  AUTO_INCREMENT,
  `item_id` INT(11),
  `item_key` VARCHAR(255),
  `item_value` VARCHAR(255),
  PRIMARY KEY (`id`)
);

INSERT INTO metas
  (`item_id`, `item_key`, `item_value`)
VALUES
  (5, 'rating', '6.4'),
  (5, 'country', 'US'),
  (6, 'rating', '7.4'),
  (6, 'country', 'UK'),
  (7, 'rating', '6.5'),
  (7, 'country', 'US'),
  (8, 'rating', '7.9'),
  (8, 'country', 'UK'),
  (5, 'genre', 'Action'),
  (5, 'year', '2019'),
  (6, 'genre', 'Drama'),
  (7, 'lang', 'EN');

Query #1

SELECT item_id,
       MAX(IF(item_key = 'rating', item_value, NULL)) AS rating
FROM metas
GROUP BY item_id
HAVING MAX(IF(item_key = 'country', item_value, NULL)) = 'US',
   AND MAX(IF(item_key = 'genre', item_value, NULL)) = 'Action'
   AND MAX(IF(item_key = 'year', item_value, NULL)) = '2019'
ORDER BY rating DESC;

Output:

| item_id | rating |
| ------- | ------ |
| 5       | 6.4    |

After using GROUP BY, you assemble "clusters of rows" or "aggregate data". You must sift through the aggregate data to isolate the row with the corresponding key to only keep THAT value (all other rows will temporarily become NULL within that expression).

View on DB Fiddle

Or just the item_id, like this: https://www.db-fiddle.com/f/tWWfSRDyXv36ZRevRivj5B/1

**I want select item_id where have country = US and rating not empty and order by rating** that is exactly what this does. Do you now need all of the values in Nick's answer? What are you doing with `SUM()` in your posted attempt. — mickmackusa, Dec 27 '19 at 01:47
all what i want is get common results and order it by rating, don't matter if get all values or not, and all your answers is work fine with me, but maybe your code will be faster because getting only one value not all, thank you — Boyzen, Dec 27 '19 at 03:27

score 1 · Answer 2 · answered Dec 26 '19 at 22:53

1

You can do it with EXISTS:

select m.* from metas m
where m.item_key = 'rating' and m.item_value is not null
and exists (
  select 1 from metas
  where item_id = m.item_id and item_key = 'country' and item_value = 'US'
)
order by m.item_value

See the demo.
Results (for your sample data):

| id  | item_id | item_key | item_value |
| --- | ------- | -------- | ---------- |
| 1   | 5       | rating   | 6.4        |
| 5   | 7       | rating   | 6.5        |

answered Dec 26 '19 at 22:53

forpas

160,666
10
38
76

how i can apply it for multi meta's like genre etc ? – Boyzen Dec 26 '19 at 22:55
You should explain this in your question with sample data and expected results. – forpas Dec 26 '19 at 22:59
i mean if i have in item_key rating,country,genre,year and i want check it also, how i can do that with your code – Boyzen Dec 26 '19 at 23:27
You will have to add similar `and exists (....)` for each of your item_key conditions. – forpas Dec 26 '19 at 23:42

Nick · Answer 3 · 2019-12-26T23:55:54.373

You can do this by using conditional aggregation and placing all your conditions in a HAVING clause:

SELECT item_id,
       MAX(CASE WHEN item_key = 'rating' THEN item_value END) AS rating,
       MAX(CASE WHEN item_key = 'country' THEN item_value END) AS country,
       MAX(CASE WHEN item_key = 'genre' THEN item_value END) AS genre,
       MAX(CASE WHEN item_key = 'year' THEN item_value END) AS year,
       MAX(CASE WHEN item_key = 'lang' THEN item_value END) AS lang
FROM metas
GROUP BY item_id
HAVING country = 'US'
   AND rating IS NOT NULL
   AND genre = 'Action'
ORDER BY rating DESC

Output:

item_id     rating  country     genre   year    lang
5           6.4     US          Action  2019    null

Demo on dbfiddle

Select with multi conditions for same column and order by

3 Answers3