SQL to find emails that have custID 0 AND custID <> 0

I have an ecommerce database that contains an 'Order' table (tblOrders). In this table there are individual orders with OrderTotal, Name, Email, etc. If the user checks out as 'guest', a CustID of 0 will be added. If a user checks out and creates or logs in as a customer, then there will be a CustID value that is not 0 (int).

I'm trying to find customers (distinct email) that have order as a logged in user (not 0) but also checked out as a guest (0). I want to find these records to assign their guest checkouts to their customer ID.

I keep trying a combination of INNER JOINs and HAVING, but I keep banging my head. Hoping for a fresh perspective.

For the sake of simplicity, here is a very stripped down schema of that table.

CREATE TABLE `tblOrders` (
    `OrderID` int NOT NULL,
    `CustID` int NOT NULL,
    `Email` varchar(200) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `tblOrders`
    ADD PRIMARY KEY (`OrderID`),
    ADD KEY `CustID` (`CustID`),
    ADD KEY `Email` (`Email`);
ALTER TABLE `tblOrders`
  MODIFY `OrderID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

INSERT INTO tblOrders(OrderID, CustID, Email)
VALUES(1,0,'test@test.com');
INSERT INTO tblOrders(OrderID, CustID, Email)
VALUES(2,1,'test@test.com');
INSERT INTO tblOrders(OrderID, CustID, Email)
VALUES(3,0,'blah@blah.com');
INSERT INTO tblOrders(OrderID, CustID, Email)
VALUES(4,0,'joe@blah.com');
INSERT INTO tblOrders(OrderID, CustID, Email)
VALUES(5,2,'joe@blah.com');
INSERT INTO tblOrders(OrderID, CustID, Email)
VALUES(6,3,'mary@blah.com');

In this example, I would look to get a record set that shows test@test.com and joe@blah.com as having both customer record and a guest record. This result set would not include blah@blah.com since that email only has a guest record and it would not show mary@blah.com because she only has a customer record (no guest).

Solution

Use group by and having for filtering:

select email
from tblOrders
group by email
having min(custID) = 0   -- ordered at least once as guest
   and max(custID) > 0   -- and at least once as non-guest

This assumes that there are no negative custIDs, which seems relevant for your use case.

There are various ways to phrase the having clause to match the requirement, here are a few alternatives:

having max(custID = 0) = 1 and max(custID > 0) = 1
having min(custID) = 0 and max(custID) <> min(custID)