CODEWITHSUNDEEP
mysql
Cathy Shapiro
Cathy Shapiro

Reputation: 74

MySQL find records with same customerid that have two different values

I have a table called orders where I track all transactions by members. This table keeps track of all changes to their registrationstatusid. I need to find members who changed their registrationstatusid from 7 to 5. But I can't figure out how to write the query. Here's what I've tried:

select memberevents.memberid, orders.registrationstatusid
FROM orders
INNER JOIN memberevents ON orders.membereventid = memberevents.id
WHERE
memberevents.eventid = 2 AND
memberevents.deletedAt IS NULL AND
orders.registrationstatusid IN (5,7)
ORDER BY m.memberid, o.updatedAt

That query brings up all records with 5 and 7, but I need just the members who have 5 and 7. If I try to do AND (orders.registrationstatusid = 5 AND orders.registrationstatusid = 7) it finds no records. Can anyone help me please?

Upvotes: 3

Views: 85

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

This is an example of a "set-within-sets" query. You can solve this with aggregation and putting the logic in the having clause:

select memberevents.memberid
FROM orders INNER JOIN
     memberevents
     ON orders.membereventid = memberevents.id
WHERE memberevents.eventid = 2 AND
      memberevents.deletedAt IS NULL
group by memberevents.memberid
having sum(orders.registrationstatusid = 5) > 0 and
       sum(orders.registrationstatusid = 7) > 0;

The only caveat: this shows members who have values of both "5" and "7". However, it does not specify the ordering of the values.

EDIT:

You can find the ordering using this string manipulation trick:

select memberevents.memberid,
       group_concat(orders.registrationstatusid) as statuses
FROM orders INNER JOIN
     memberevents
     ON orders.membereventid = memberevents.id
WHERE memberevents.eventid = 2 AND
      memberevents.deletedAt IS NULL
group by memberevents.memberid
having concat(',', statuses, ',') like '%,7,5,%';

I realize that the following having clause also works:

having find_in_set(5, statuses) = find_in_set(7, statuses) + 1 and
       find_in_set(7, statuses) > 0;

Upvotes: 1

Related Questions