Reputation: 55
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.
Upvotes: 3
Views: 1010
Reputation: 522817
You can use pivoting logic to find the values you want for each key. The subquery aliased as d2
aggregates by post and will return only posts whose payment method is Visa. We then join your original data
table to this subquery to restrict to only posts you want to see.
SELECT
d1.post_id,
d1.meta_key,
d1.meta_value
FROM data d1
INNER JOIN
(
SELECT post_id
FROM data
GROUP BY post_id
HAVING MAX(CASE WHEN meta_key = 'payment_method' THEN meta_value END) = 'visa'
) d2
ON d1.post_id = d2.post_id
WHERE
d1.meta_key IN ('payment_method', 'order_total')
ORDER BY
d1.post_id,
d1.meta_key DESC;
Upvotes: 1
Reputation: 74740
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'
Upvotes: 2
Reputation: 13026
you can use subquery
and group by
select post_id
, meta_key
, meta_value
from data
where post_id in
(select post_id
from data
where meta_key in ('payment_method', 'order_total') and meta_value='visa'
group by post_id
having count(1) = 2)
order by post_id, meta_value desc
Upvotes: 1