How to get parts from table part and not exist on both tables chemical missing and chemical master ?

Question

I work on sql server 2012 i need to get parts from table part and not exist on both tables chemical missing and

chemical master by using exist or not exist and if there are more ways please help me

I need to rewrite these statement with another statement using

not exist or exists and if there are any way I need it

SELECT  np.PartNumber, np.CompanyID
FROM parts.nop_part np
INNER JOIN Z2DataCompanyManagement.CompanyManagers.Company c WITH(NOLOCK) ON np.CompanyID = c.CompanyID
LEFT  JOIN parts.chemicalmaster cm ON cm.PartID = np.PartID
LEFT  JOIN Parts.ChemicalMissingParts cmp ON cmp.PartID = np.PartID
WHERE cm.ChemicalID IS NULL AND cmp.ChemicalMissingPartID IS NULL

so How to rewrite by using exist or not exist and if there are other way tell me ?

Answer 1

Hi @ahmed salah ，

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

You could also refer below methods and check whether any of them is helpful.

Method 1:

 SELECT  np.PartNumber, np.CompanyID  
 FROM parts.nop_part np  
 INNER JOIN Z2DataCompanyManagement.CompanyManagers.Company c WITH(NOLOCK) ON np.CompanyID = c.CompanyID  
 WHERE NOT EXISTS   
 (SELECT 1 FROM parts.chemicalmaster cm  LEFT JOIN Parts.ChemicalMissingParts cmp ON cmp.PartID = cm.PartID  
 WHERE cm.PartID=np.PartID  
 )  

Method 2:

  SELECT  np.PartNumber, np.CompanyID  
 FROM parts.nop_part np  
 INNER JOIN Z2DataCompanyManagement.CompanyManagers.Company c WITH(NOLOCK) ON np.CompanyID = c.CompanyID  
 WHERE NOT EXISTS   
 (SELECT 1 FROM parts.chemicalmaster cm    
 WHERE cm.PartID=np.PartID  
 )  
 AND NOT EXISTS   
 (SELECT 1 FROM Parts.ChemicalMissingParts cmp    
 WHERE cmp.PartID=np.PartID  
 )  

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

To answer the question on how to rewrite the SQL statement using NOT EXISTS or EXISTS, and exploring other methods to achieve the same result, let's focus on the requirement: finding parts that do not exist in either the chemicalmaster or ChemicalMissingParts tables. Here's how you can achieve this with different approaches:

Using NOT EXISTS:

sqlCopy code
SELECT

This query selects parts that do not have a corresponding entry in either the chemicalmaster or ChemicalMissingParts tables. The NOT EXISTS clause is used here to check for the absence of related records in these tables.

Using EXCEPT:

Another way to achieve the desired outcome is to use the EXCEPT keyword, which returns distinct rows from the left query that aren't found in the right query:

sqlCopy code
SELECT

This approach first selects all parts, then removes those that are found in the chemicalmaster and ChemicalMissingParts tables. It's important to note that EXCEPT effectively removes duplicates and may not be supported in all database systems or versions.

Using LEFT JOIN and Filtering on NULL:

The original question's query already uses this method effectively. By left joining chemicalmaster and ChemicalMissingParts and filtering out rows where chemicalmaster and ChemicalMissingParts have matching entries, it achieves the desired result. This method is often preferred for its readability and ease of understanding:

sqlCopy code
SELECT

Each of these methods can be used to achieve the same outcome, depending on your preference for readability, performance, and compatibility with your SQL server version.