Two Rows With the Same Id and Two Different Values, Getting the Second Value into Another Column

select rows with same ID but different value in another column between two tables

select t1.*
from t1
join t2 on t1.id = t2.id 
where t1.active <> t2.active

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

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;

Demo

Getting a value within multiple rows with same id in dataframe

First filter CO values by Series.eq in boolean indexing and then I get first rows by id by DataFrame.drop_duplicates:

print (df[df['z_type'].eq('CO')])
   id a_code a_type z_code z_type
0   1    abc     CP  abclm     CO
1   1  abclm     CO  wedvg     CO
2   2    pqr     CP  pqren     CO
3   2  pqren     CO  unfdc     CO
5   3  lmnre     CP  wqrtn     CO

df1 = (df[df['z_type'].eq('CO')]
       .drop_duplicates('id')
       .rename(columns={'z_code':'code'})[['id','code']])
print (df1)
   id   code
0   1  abclm
2   2  pqren
5   3  wqrtn

Returning rows with the same ID but exclude some on second column

You can use group by and having:

select id
from t
group by id
having sum(case when value = 3 then 1 else 0 end) = 0;

The having clause counts the number of "3"s for each id. The = 0 returns only returns groups where the count is 0 (i.e. there are no "3"s).

---

Related Topics