SQL mutually exclusive conditions-CodePudding

EDIT:

I have a table 'TableA' with columns product, feature1, feature2 I have another table 'TableB' with columns 'saleorder, feature1, feature2'

Currently I use an inner join to show products with matching column values

SELECT distinct a.product 
FROM TableA a
    INNER JOIN TableB b
    ON a.feature1 = b.feature1 
    AND a.feature2 = b.feature2

Currently this shows me products from TableA that had an exact match for feature1,feature2 from TableB.

I want to modify this so that, I see those products from TableA which did not match for feature1,feature2. In a way the opposite output of the original script, showing only those products for which no saleorder with feature1 and feature2 exists.

How can I do this using T-SQL?

CodePudding user response：

from TableA which did not match for feature1 and feature2, but matched for feature3:

SELECT distinct a.feature3, a.feature1, a.feature2, b.feature1, b.feature2
FROM TableA a
    INNER JOIN TableB b
    ON a.feature3 = b.feature3  
    AND a.feature1 <> b.feature1
    AND a.feature2 <> b.feature2

CodePudding user response：

Step 1: Get all the rows from the TableA with feature1 and feature 2 from TableB having NULL for rows that does not have corresponding matching values for 'feature1' and 'feature2' from TableA.

SELECT A.product,
       A.feature1 Afeature1,
       A.feature2 Afeature2,
       B.feature1 Bfeature1,
       B.feature2 Bfeature2
FROM   tablea A
       LEFT OUTER JOIN tableb B
                    ON A.feature1 = B.feature1
                       AND A.feature2 = B.feature2;

--- this is will have output like this

product  Afeature1   Afeature2    Bfeature1     Bfeature2 
abcd     l           l             m             m         
efgh     x           y             NULL          NULL    -----> this is what you want.

Step 2: So now you just have to filter out the NOT NULL's for Columns from TableB that is obtained in the earlier output.

SELECT productA,Afeature1,Afeature2
FROM   (SELECT A.product productA,
               A.feature1 Afeature1,
               A.feature2 Afeature2,
               B.feature1 Bfeature1,
               B.feature2 Bfeature2
        FROM   tablea A
               LEFT OUTER JOIN tableb B
                            ON A.feature1 = B.feature1
                               AND A.feature2 = B.feature2)
WHERE  bfeature1 IS  NULL
       AND bfeature2 IS NULL;

Final output:

product  Afeature1   Afeature2     
efgh     x           y

CodePudding user response：

You can use a NOT EXISTS if you don't need columns from TableB

SELECT DISTINCT product 
FROM TableA p
WHERE NOT EXISTS
(
    SELECT 1
    FROM TableB s
    WHERE s.feature1 = p.feature1 
      AND s.feature2 = p.feature2 
)