Mysql: How can I retrieve two rows with same id but different values in column

Question

How can I retrieve two rows with same id and same value in column and other variable. Here is the table 'data'

+---------+----------------+-------------+
| post_id | meta_key       | meta_value  |
+---------+----------------+-------------+
| 1000    | payment_method | visa        |
| 1000    | other          | sometext    |
| 1000    | order_total    | 65.00       |
| 1000    | etc            | sometext2   |
| 1001    | payment_method | bacs        |
| 1001    | other          | sometext    |
| 1001    | order_total    | 105.00      |
| 1001    | etc            | sometext2   |
| 1002    | payment_method | visa        |
| 1002    | other          | sometext    |
| 1002    | order_total    | 28.00       |
| 1002    | etc            | sometext2   |
| ...     | ...            | ...         |
+---------+----------------+-------------+

As you can see payment_method has stable value and order_total is variable.

I tried:

SELECT * FROM 'data' WHERE meta_key IN ('payment_method', 'order_total') GROUP BY post_id, meta_key

output

+---------+-----------------+------------+
| post_id | meta_key        | meta_value |
+---------+-----------------+------------+
| 1000    | payment_method  | visa       |
| 1000    | order_total     | 65.00      |
| 1001    | payment_method  | bacs       |
| 1001    | order_total     | 105.00     |
| 1002    | payment_method  | visa       |
| 1002    | order_total     | 28.00      |
| ...     | ...             | ...        |
+---------+-----------------+------------+

I only want payment_method = visa and his respective amount by post_id.

+---------+-----------------+------------+
| post_id | meta_key        | meta_value |
+---------+-----------------+------------+
| 1000    | payment_method  | visa       |
| 1000    | order_total     | 65.00      |
| 1002    | payment_method  | visa       |
| 1002    | order_total     | 28.00      |
| ...     | ...             | ...        |
+---------+-----------------+------------+

Thank you.

Answer 1

The "problem" you have is that the data that is related is on different rows, which essentially makes it unrelated. You'll need to join this table to itself, or carry out a pivot operation to make the data on the same row

Assuming you want the output exactly as you show:

SELECT * FROM data WHERE meta_key = 'payment_method' and meta_value = 'visa'

UNION ALL

SELECT a.* 
FROM data a
INNER JOIN data v 
ON
  v.meta_key = 'payment_method' and 
  v.meta_value = 'visa' and 
  a.meta_key = 'order_total' and 
  a.id = v.id

The magic happens in the second query after the union - we are again selecting all the visa rows (aliased as v) just like the query before the union but this time we are using it as a filter to restrict the order_total rows (aliased as a). The v table contains only ids for visa, so when joined back on ID it filters the a table to only the same set of visa IDs. The rows in a are only order total rows, and we exclude all the v row information by only selecting a.*

---

This query is an alternative form that might be easier to understand:

SELECT * 
FROM data 
WHERE 
  meta_key in ('payment_method', 'order_total') and 
  ID in (SELECT x.id FROM data x WHERE x.meta_key = 'payment_method' and x.meta_value = 'visa')

It's effectively the same thing; create a list of ID for visa and then use it to filter the results to "only those ids" and also "only the payment method and order total rows"

If you ultimately want the data on the same row it might be better to pivot it right now with something like:

SELECT id, 'visa' as payment_method, max(case when meta_key = 'order_total' then meta_value end) as order_total
FROM data 
WHERE meta_key IN ('payment_method', 'order_total')
GROUP BY id 
HAVING max(case when meta_key = 'payment_method' then meta_value end) = 'visa'