Is there any way to make FULL OUTER JOIN table match with the correct record?

Question

I'm trying to join two table using FULL OUTER JOIN that two table have different row of data and the column between this two table are same.

Table 2 FULL OUTER JOIN Table 1

Table 1

id     name         Payment Amount
=== ======== =====================
1    Jack           10000
2    May            20000
3    Amy            30000

Table 2

id     name         Payment Amount                AccountID
=== ======== ==================== ============
1    Jack           10000                                 000001
2    Amy            30000                                000002

Output that show after execute

id     T1name     T2name     Payment Amount               AccountID
=== ======== ======== ==================== ============
1    Jack          Jack          10000                                  000001
2    May           Amy          20000                                  000002
3    Amy                            30000

Output that I expect

id     T1name     T2name     Payment Amount               AccountID
=== ======== ======== ==================== ============
1    Jack          Jack          10000                                  000001
2    May                            20000
3    Amy           Amy          30000                                  000002

The table is order by Payment amount.

"after I execute the query" - the query you've *not shown us in any way*? We can't debug code we can't see (usually) — Damien_The_Unbeliever, Apr 08 '19 at 06:47
@Damien_The_Unbeliever, i just want to know any idea i can solve this problem, later i will edit my grammar — Jack Fire, Apr 08 '19 at 06:50
Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Apr 08 '19 at 09:48

score 3 · Answer 1 · answered Apr 08 '19 at 06:29

CREATE TABLE #Table1
    ([id] varchar(2), [name] varchar(4), [Payment Amount] int)

INSERT INTO #Table1
    ([id], [name], [Payment Amount])
VALUES
    ('S1', 'Jack', 10000),
    ('S2', 'May', 20000),
    ('S3', 'Amy', 30000)

CREATE TABLE #Table2
    ([id] varchar(2), [name] varchar(4), [Payment Amount] int)
;     
INSERT INTO #Table2
    ([id], [name], [Payment Amount])
VALUES
    ('X1', 'Jack', 10000),
    ('X2', 'Amy', 30000)

select A.id,A.name T1name ,isnull(B.name,'') T2name,A.[Payment Amount] from #Table1 A  left join #Table2 B on A.name=B.name
and A.[Payment Amount]=B.[Payment Amount]

output

id  T1name  T2name  Payment Amount
S1  Jack    Jack      10000
S2  May               20000
S3  Amy     Amy       30000

Sorry @Chanukya i had edited my case. i found that something that i ask was wrong previously. — Jack Fire, Apr 08 '19 at 06:46

score 0 · Answer 2 · answered Apr 08 '19 at 06:30

You should always JOIN with primary key(specifically keys) or with unique key always. otherwise you will get duplicate values. Name column may not be unique and you will get Cartesian product .In your case in order to get your desired results you should join on t1.name=t2.name

Is there any way to make FULL OUTER JOIN table match with the correct record?

2 Answers2