CODEWITHSUNDEEP
sqlsql-serversql-server-2008
nightfire001
nightfire001

Reputation: 779

Select rows not in another table by comparing two table

I have following two tables TableA and TableB

TableA

Id  Month_Id    Customer_Id   Total_Amount
1    1          1               50
2    2          1              150
3    3          1              200
4    1          2               75
5    2          2              100
6    1          3              400
7    2          3              200

TableB

Id  Month_Id    Customer_Id         Total_Amount
1     1               1             50
2     2               1            150
3     1               2             75

I want to compare Month_Id Customer_Id Total_Amount in both tables and select Id from TableA. The output should be as follow.

Output

Id
3
5
6
7

My concept is:

SELECT TableA.Id FROM TableA 
WHERE TableA.Month_Id <> TableB.MonthId AND
TableA.Customer_Id <> TableB.Customer_Id AND
TableA.Total_Amount <> TableB.Total_Amount

Upvotes: 0

Views: 126

Answers (7)

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2744

In oracle sql it would be:

SELECT ID FROM
   (SELECT ID, Month_Id, Customer_Id, Total_Amount FROM TABLE_A
    MINUS 
    SELECT ID, Month_Id, Customer_Id, Total_Amount FROM TABLE_B);

Is this what you want? (Not sure of MINUS operator in sql-server though)

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17146

SELECT Id FROM TableA A LEFT JOIN tableB B 
    ON  A.Id=B.Id AND A.Month_Id =B.Month_Id  
    AND A.Customer_Id   =B.Customer_Id  
    AND A.Total_Amount=b.Total_Amount
WHERE B.Id is NULL

Upvotes: 0

GercoOnline
GercoOnline

Reputation: 291

    SELECT TableA.Id
    FROM TableA
    WHERE NOT EXISTS (
      SELECT 1
      FROM TableB
      WHERE TableB.Month_Id = TableA.Month_Id
      AND TableB.Customer_Id = TableA.Customer_Id
      AND TableB.Total_Amount = TableA.Total_Amount
   )

Upvotes: 1

Mureinik
Mureinik

Reputation: 312219

You can use the EXCEPT set operator:

SELECT id 
FROM   (SELECT * FROM table_a
        EXCEPT
        SELECT * FROM table_b) t

Upvotes: 1

NiiL
NiiL

Reputation: 2827

SELECT id FROM
     (SELECT id, month_id, customer_id, total_ammount FROM TableA
      EXCEPT    
      SELECT id, month_id, customer_id, total_ammount FROM TableB);

Upvotes: 1

Mzf
Mzf

Reputation: 5260

You can use Merge with WHEN NOT MATCHED

place your condition in ON <merge_search_condition>

Upvotes: 0

cetver
cetver

Reputation: 11829

select Id 
from (
select Id, Month_Id,    Customer_Id,   Total_Amount from TableA    
except     
select Id, Month_Id,    Customer_Id,   Total_Amount from TableB
) q

Upvotes: 1

Related Questions