Joining poorly designed SQL tables?

Question

I've tried searching for information on joining tables without foriegn keys but it seems the answer is always to create the foreign key. I cannot modify the tables in question to do this and I must report on data that is already in production. The following is a portion of the data in the tables involved in order to exemplify the issue.

Table A
Journal    Account    Debit    Credit    Sequence
--------------------------------------------------
87041      150-00     100.00   0.00      16384
87041      150-10     0.00     100.00    32768
87041      150-00     50.0     0.0       49152
87041      210-90     0.0      50.0      65536

Then the second table, tracking additional bits of information, is largely the same but missing the Sequence number that would tie the line items together properly. It has its own Sequence Number that is unrelated.

Table B
Journal    Account    Label    Artist    Sequence
--------------------------------------------------
87041      150-00     Label02  Artist12  1
87041      150-10     Label09  Artist03  2
87041      150-00     Label04  Artist01  3
87041      210-90     Label01  Artist05  4

At present the best I can come up with is to join on Journal and Account but that duplicates records. I have gotten close by playing around with grouping and max() on sequence number but the result has been that that not all duplicates are removed for journal entries with a very large number of rows and the first match from the second table is always displayed for lines that have the same account.

Closest - but bad - result
Journal    Account    Debit    Credit    Sequence    Label    Artist
----------------------------------------------------------------------
87041      150-00     100.00   0.00      16384       Label02  Artist12
87041      150-10     0.00     100.00    32768       Label09  Artist03
87041      150-00     50.0     0.0       49152       Label02  Artist12  <-- wrong 
87041      210-90     0.0      50.0      65536       Label01  Artist05

How can I join the tables such that duplicates are excluded but also so that the correct Label and Artist are displayed? It sort of feels like I have to produce a query which knows that one of the records from Table B has already been used when the 49152 record from Table A comes looking for a match.

EDIT:

@Justin Crabtree A.Sequence will be the order in which the line items were entered. So a user could have entered the last line in the example first, then the first line, then the third, and finally the second.

@Edper Microsoft SQL Server...hmm, I cannot remote into the client's machine this morning...otherwise I would provide the version.

@Abe Miessler yes, you are correct.

As soon as I can get back into the server I will try your suggestion @pkuderov

Answer 1

Try this

;WITH a AS
(
    SELECT Journal,
           Account,
           Debit,
           Credit,
           Sequence,
           Id = ROW_NUMBER() OVER(PARTITION BY Journal ORDER BY Sequence)
    FROM dbo.tablea
)
, b AS
(
    SELECT Journal,
           Account,
           Label,
           Artist,
           Id = ROW_NUMBER() OVER(PARTITION BY Journal ORDER BY Sequence)
    FROM dbo.tableb
)

SELECT a.Journal,
       a.Account,
       a.Debit,
       a.Credit,
       a.Sequence,
       b.Label,
       b.Artist
FROM a
JOIN b ON b.Journal = a.Journal
      AND b.Account = a.Account
      AND b.Id = a.Id

Answer 2

Hi, that's just an idea:

select
    a.Journal, a.Account, a.Debit, a.Credit, a.Sequence, b.Label, b.Artist
from (
    select
        *, 
        row_number() over(partition by Journal, Account order by Sequence) as idInGroup
    from a
) as a
join (
    select
        *, 
        row_number() over(partition by Journal, Account order by Sequence) as idInGroup
    from b
) as b on
    a.Journal = b.Journal
    and a.Account = b.Account
    and a.idInGroup = b.idInGroup

Here I assume that orders appeared in Sequence order (in both tables) and that's the base hint for join tables.