SQL How to return only the results where a condition did not occur on the result

Question

SELECT DISTINCT SalesPerson ,Store ,SaleNumber FROM Records WHERE Store = '1' results: ![34370-image.png][1] I have records where one store has 4 sales people and multiple sales people can be listed on the same sale number. When I search all sales and all sales people each row is duplicated to represent everyone on the sale (shown above). How can I limit the results to only sales that Greg was NOT on? I tried to do it like this: SELECT DISTINCT SalesPerson ,Store ,SaleNumber FROM Records WHERE Store = '1' and SalesPerson <> 'Greg" Results: ![34419-image.png][2] But the results I received only removed the rows with 'Greg' (shown above) and still left the sales that included him. I would like to receive results like this: ![34431-image.png][3] Any help is GREATLY appreciated! Also I can NOT delete results from my database. [1]: /api/attachments/34370-image.png?platform=QnA [2]: /api/attachments/34419-image.png?platform=QnA [3]: /api/attachments/34431-image.png?platform=QnA

Answer 1

Hi anonymous user,

Please refer below several methods and check whether any of them is helpful to you.

You could also check the execution plan from your side and choose the fastest one.

 SELECT *  
 FROM Records  
 WHERE Store = '1'  
  AND SaleNumber NOT IN   
  (SELECT DISTINCT SaleNumber FROM Records WHERE SalesPerson='Greg')  
  
 SELECT *  
 FROM Records P  
 WHERE P.Store = '1'  
 AND NOT EXISTS   
 (SELECT 1 FROM Records O WHERE SalesPerson='Greg' AND o.salenumber=p.salenumber)  
  
  SELECT P.*  
 FROM Records P  
 LEFT JOIN (SELECT O.Store,O.salenumber FROM Records O WHERE O.salesperson='Greg') A  
 ON P.salenumber=A.salenumber  AND P.store=A.store  
 WHERE P.Store = '1' AND A.salenumber IS NULL  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi anonymous user , I think this will work for you.

SELECT DISTINCT SalesPerson
 ,Store
 ,SaleNumber
FROM Records
WHERE Store = '1'
 AND SaleNumber NOT IN (SELECT DISTINCT SaleNumber FROM Records WHERE SalesPerson='Greg')

Answer 3

Hi anonymous user,

Actually our queries are working with your provided DDL and sample data at the beginning.

If you still face any issue with your actual data and query, it could be better for you to provide your actual DDL and sample data if possible.

You could try with below query and check whether it is working with your actual data.

Please note that you need to distinguish the alias of all tables in your subquery.

  SELECT DISTINCT  
           i.IncidentNumber  
          ,i.BeatName  
          ,u.CallSign      
     FROM MV_Incident i  
       INNER JOIN MV_IncidentUnits u ON i.IncidentKey = u.IncidentKey  
     WHERE  
           i.IncidentNumber NOT IN (SELECT DISTINCT a.IncidentNumber FROM MV_Incident a WHERE a.agencyid = 'mdfd')  
           AND i.BeatName = 'md'  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.