CODEWITHSUNDEEP
sqlms-access
Anup
Anup

Reputation: 107

How to find non-matching records between 2 tables not having any unique keys

I have 2 tables tblBudget and tblActuals.

tblBudget 

 ProjID ExpenseType OrigBudget
 101    Furniture   5000
 102    Hardware    2000
 102    Software    3500

tblActuals

ProjID ExpenseType ActualExpense 
101    Furniture    4000
101    Hardware     2500     
102    Hardware     1500

I want to pull the matching and non-matching records into a single query so that it shows like this

ProjID  ExpenseType  OriginalBudget   ActualExpense
101     Furniture     5000            4000          
101     Hardware         0            2500
102     Hardware      2000            1500
102     Software      3500               0

I tried a join query which successfully selects the matching records but having trouble in selecting the non-matching records. My table does not have primary keys nor can they be added since its part of a huge company db. Any help from left join experts will be highly appreciated

Upvotes: 0

Views: 89

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

MS Access does not make this easy. But here is one approach that should do what you want:

select b.ProjId, b.ExpenseType, b.OriginalBudget,
       nz(a.ActualExpense, 0) as ActualExpense
from tblBudget as b left join
     tblActual as a
     on b.ProjID = a.ProjId and b.ExpenseType = a.ExpenseType
union all
select a.ProjId, a.ExpenseType, 0 as OriginalBudget, a.ActualExpense
from tblActual as a left join
     tblBudget as b
     on b.ProjID = a.ProjId and b.ExpenseType = a.ExpenseType
where b.ProjId is null

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

In mysql use LEFT/RIGHT JOIN to emulate FULL OUTER JOIN

SQL DEMO

SELECT tB.ProjID,  tB.ExpenseType,  tB.OrigBudget as OrigianlBudget,  
       COALESCE(tA.ActualExpense,0) as ActualExpense
FROM tblBudget tB
LEFT JOIN tblActuals tA
  ON tB.`ExpenseType` = tA.`ExpenseType`
 AND tB.`ProjID` =  tA.`ProjID`

 UNION  

SELECT COALESCE(tB.ProjID, tA.ProjID),  
       COALESCE(tB.ExpenseType, tA.ExpenseType),  
       COALESCE(tB.OrigBudget, 0),
       tA.ActualExpense
FROM tblBudget tB
RIGHT JOIN tblActuals tA
  ON tB.`ExpenseType` = tA.`ExpenseType`
 AND tB.`ProjID` =  tA.`ProjID`
ORDER BY `ProjID`

OUTPUT

enter image description here

Upvotes: 2

Related Questions