Join Question (1 Viewer)

[dapfeifer]

Hey all,

I've got a couple of tables that contain numerical data that I am finding the difference between. Both are defined by the same PK, and with an inner join the query correctly shows the differences of all the PKs that exist in both tables. However, I need to have the query also show the non-matching PKs from both tables and their corresponding values (which will end up being positive or negative depending on which table they come from). A left or right join didn't work either, so any input on how I might go about this would be appreciated! Perhaps a union needs to be used?

Here is the query at the moment:

SELECT DISTINCT tblCurrentYTD.Payor_Code, tblCurrentYTD.Payor_Name, tblCurrentYTD.SRCol, tblCurrentYTD.Inbound_Bytes, tblLastYTD.Inbound_Bytes, tblCurrentYTD.Inbound_Bytes-tblLastYTD.Inbound_Bytes AS [Inbound Diff], tblCurrentYTD.Outbound_Bytes-tblLastYTD.Outbound_Bytes AS [Outbound Diff], tblCurrentYTD.Bytes-tblLastYTD.Bytes AS [Bytes Diff], tblCurrentYTD.Billable_Bytes-tblLastYTD.Billable_Bytes AS [Billable Diff], tblCurrentYTD.Amount-tblLastYTD.Amount AS [Amount Diff]
FROM tblCurrentYTD INNER JOIN tblLastYTD ON (tblCurrentYTD.Payor_Code = tblLastYTD.Payor_Code) AND (tblCurrentYTD.SRCol = tblLastYTD.SRCol);

I realize my wording may not be the clearest, so if you need me to clarify just say so. Thanks ahead of time!

 

[rainman89]

yeah if you use a union, join the query where they all have them with the ones that dont and you will get all of em