error on results for sql join

Question

I'm joining two tables and it appears that each payment is being associated to a payee once, I think this is because of a join.

Say I have an account with two payments on it, and two different payees. The data is contained in two tables

Payment: 35.00, Payee: Dave
Payment: 40.00, payee: Mark

Table1

PK, Payment, Account number

35.00
40.00

Table2

PK, Payee, Account number

Mark
Dave

Query:

select distinct C.Payment, B.Payee
from table1 C
join Table2 B on check.PK = trans.PK
where check.accountnumber like ''123456''

Results looks like this

35.00 Mark
35.00 Dave
40.00 Mark
40.00 Dave

Each name gets its own record of a payment which is inaccurate, any advice?

Answer 1

Looks like you are just cross joining one table with another and getting back every possible combination of the two tables. Try joining on accountnumber instead. The PK value in each table have nothing to do with each other and should not be joined on here.