MySQL to return unique rows of members who have visited one page but not in another

Question

The database stores the "email" and "page" of every "visit". I want to find the emails of the people who have visited the "checkout" page , possibly many times, but have NOT visited the "Thankyou" page. i.e abandoned carts; they got as far as checkoutm but never completed the purchase to get to the "Thankyou" page.

I want to return the list:

bob@bob.com  
di@di.com

as the only two emails that have NOT visited the "Thankyou" page.

I know it must be some kind of self join on the visit table, but I'm struggling. Here is the data:

visit_id | visit_email     | visit_page
----------------------------------------
1        | alice@alice.com | checkout
2        | alice@alice.com | Thankyou
3        | alice@alice.com | checkout
4        | alice@alice.com | checkout
5        | bob@bob.com     | checkout
6        | chuck@chuck.com | checkout
7        | chuck@chuck.com | Thankyou
8        | di@di.com       | checkout
9        | bob@bob.com     | checkout



CREATE TABLE `visit` (
  `visit_id` int(11) NOT NULL,
  `visit_email` varchar(80) NOT NULL,
  `visit_page` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `visit`
--

INSERT INTO `visit` (`visit_id`, `visit_email`, `visit_page`) VALUES
(1, 'alice@alice.com', 'checkout'),
(2, 'alice@alice.com', 'Thankyou'),
(3, 'alice@alice.com', 'checkout'),
(4, 'alice@alice.com', 'checkout'),
(5, 'bob@bob.com', 'checkout'),
(6, 'chuck@chuck.com', 'checkout'),
(7, 'chuck@chuck.com', 'Thankyou'),
(8, 'di@di.com', 'checkout'),
(9, 'bob@bob.com', 'checkout');

score 2 · Answer 1 · answered Dec 17 '19 at 20:42

2

You can use this:

select distinct v.visit_email 
from visit v 
where (
    select count(*) 
    from visit c 
    where c.visit_email = v.visit_email 
    and c.visit_page = 'Thankyou'
) = 0

Output:

bob@bob.com
di@di.com

answered Dec 17 '19 at 20:42

sf_admin

577
4
11

score 1 · Answer 2 · answered Dec 17 '19 at 20:22

I want to find the emails of the people who have visited the "checkout" page , possibly many times, but have NOT visited the "Thankyou" page

One option is to use aggregation and filter with a having clause:

select visit_email
from visit
group by visit_email
having
    max(visit_page = 'checkout') = 1
    and max(visit_page = 'Thankyou') = 0

MySQL to return unique rows of members who have visited one page but not in another

2 Answers2