Joining table based on Primary key and then comparing attribute

Transact-SQL
1

Afternoon Team,
Apologise for reaching out to the forum but i am having a night mare with something basic .

Aim : join two tables together based on the AccountCode and then compare the status between the two tables with a helper column to determine if its a match.

Table 1 :
select
AccountCode__c,
STATUS__C
from CurrentP

Table 2 :
select
AccountCode__c,
STATUS__C
from Old

the expected outcome would be something

AccountCode__c Old.STATUS__C Current.STATUS__C Outcome
1111 Open Open TRUE
2222 Open Closed FALSE
3333 Close Active FALSE

Looking forward to your help

2

Welcome! No need to apologize.
What have you tried so far?

3

Its trying my patience lol.

Well i tried an inner join .

select
CurrentP.AccountCode__c,
Old.AccountCode__c
from Old
inner join CurrentP
on Old.AccountCode__c = CurrentP.AccountCode__c

But for some reason it’s keep throwing an error. I thought it might be access to the one of the tables. But I can query each table so it must be something wrong with my join.

Once I got the join I think il be okay

4

I assumed there could be more than one row in Old and that, if so, you would want the latest row to compare to. Change the ?...? to the name of the date column that tells when the row in Old was added.

SELECT
C.AccountCode__c,
O.STATUS__C AS [Old.STATUS__C],
C.STATUS__C AS [Current.STATUS__C],
CASE WHEN O.STATUS__C = C.STATUS__C THEN 'TRUE' ELSE 'FALSE' END AS Outcome
FROM CurrentP C
CROSS APPLY (
    SELECT TOP (1) *
    FROM Old O
    WHERE O.AccountCode__c = C.AccountCode__c
    ORDER BY O.?date_column? DESC
) AS O
5

@ScottPletcher
In old and current there is only ever one line item for n account code. There is no duplicate account account codes in either table so a straight inner join should suffice with a case statement

6

You can use Scott's query and just add an inner join to your old table in place of the cross apply, in case of only one record for each account, try that first it should work. By the way, you did not post the error you are getting, your query seems to be fine as well but it's not complete (with other columns), maybe there is a typo or a table/column name mismatch.