Complex SQL query Issues-CodePudding

I have a problem selecting the data from a table. I'm sure the answer is staring me in the face, but my brain is mush. We're trying to find out which customers have bought based on only email blasts and nothing else. I can easily select any row where salesPrompt = 'email' but that might still include folks that also bought due to salesPrompt = 'postcard' or some other method. How do I select only customers that bought due the email blast? I've tried:

SELECT * FROM `customer_sales` WHERE `salesPrompt` = 'email'  GROUP BY `accountID`

But that still brought up customers that had salesPrompt equaling other values.

CodePudding user response：

you should check for count distint salePrompt using having and join the result

select s.* 
FROM `customer_sales` 
inner join  (
  SELECT accountID 
  FROM `customer_sales` 
  GROUP BY `accountID`
  having count(distinct  salesPrompt) = 1
 ) t on t.accountID = s.accountID
where  `salesPrompt` = 'email'

CodePudding user response：

Another possibility is to select all entries having the desired salesPrompt and then exclude (using EXISTS) all entries that also have further salesPrompts and the same accountid. Assuming, you want to name the main selection "target", this looks like this:

SELECT salesprompt, accountid
FROM customer_sales AS target WHERE salesprompt = 'email'
AND NOT EXISTS (SELECT 1 FROM customer_sales WHERE salesprompt != 'email' 
AND accountid = target.accountid);