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

Mysql – how to select rows that has same value in one column but different value in another column

My table has 3 columns as mid, campaign name and status. I want to select a mid that should have specified campaign name and has different status for that same mid.

For eg output should be like below –

mid campaign_name status
50f07d3be5e911b fy24_postal primary campaign_all segments delivered
50f07d3be5e911b fy24_postal primary campaign_all segments delivered
50f07d3be5e911b fy24_postal primary campaign_all segments shipped

I tried joins, distinct keyword but unable to get desired output.

SELECT * FROM db_6sense_campaign_member_status
   WHERE mid IN (
      SELECT T1.mid
      FROM db_6sense_campaign_member_status T1 INNER JOIN
      db_6sense_campaign_member_status T2 ON T1.mid = T2.mid
      WHERE T1.status <> T2.status
   )  and campaign_name = 'fy24_postal primary campaign_all segments'  order by mid

I would appreciate a help around this, thanks.

Tags:

2

Answers


  1. 0 
    SELECT mid
FROM db_6sense_campaign_member_status
WHERE campaign_name = 'fy24_postal primary campaign_all segments'
GROUP BY 1
HAVING COUNT(DISTINCT status) > 1
    Login or Signup to reply.
  2. 0

    It seems that you are retrieving rows with different statuses for the same campaign name. You need to have edits in the code:

    SELECT T1.mid, T1.campaign_name, T1.status
    FROM db_6sense_campaign_member_status T1
    INNER JOIN db_6sense_campaign_member_status T2 ON T1.mid = T2.mid
    WHERE T1.status <> T2.status
    AND T1.campaign_name = ‘fy24_postal primary campaign_all segments’ ORDER BY T1.mid;

    I hope this code will work for you!!

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

Back To Top
Search