-1

I face issue about duplicate data when join table, here my sample data table I have

-- Table A

enter image description here

I want to join with

-- Table B

enter image description here

this my query notation for join both table,

select a.trans_id, name 
from tableA a 
inner join tableB b 
   on a.ID_Trans = b.trans_id

and this the result, why I get the duplicating data which should show only two lines of data, please help me to solve this case.

enter image description here

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Indra suandi
  • 141
  • 1
  • 3
  • 15
  • 3
    You are getting all matches between the two tables. The results are exactly what you are asking for. – Gordon Linoff Apr 06 '21 at 16:19
  • 1
    because you have two rows in table A and two rows in table B that matches so you get 4 rows – eshirvana Apr 06 '21 at 16:20
  • as quick and DIRTY fix select distinct a.trans_id, b.name from tableA a inner join tableB b on a.trans_id = b.trans_id – Sergey Apr 06 '21 at 16:20
  • @eshirvana it should be merge, not 4 rows, but only 2 rows, – Indra suandi Apr 06 '21 at 16:21
  • Seems like the problem is your data in `tableA`. – Thom A Apr 06 '21 at 16:22
  • *"it should be merge, not 4 rows, but only 2 rows, "* that isn't how a `JOIN` works. Perhaps what you *really* want is an `EXISTS`? – Thom A Apr 06 '21 at 16:22
  • @Sergey I try to find out other solution – Indra suandi Apr 06 '21 at 16:22
  • then you need to remove duplicate from tableA,because for 2 records in the tableA you have two corresponding records in the tableB.The result of JOIN is 4 records – Sergey Apr 06 '21 at 16:26
  • @Larnu so, what u suggest, I cannot remove duplicate row in tableA thisa above only sample for my case tableA has any informations ? – Indra suandi Apr 06 '21 at 16:28
  • What do the rows in table A represent? What do the rows in table B represent? Why do you need table A in your query? Why not just `select trans_id, name from tableb`? – Thorsten Kettner Apr 06 '21 at 16:30
  • @ThorstenKettner it just sample, actually tableA has many information I need – Indra suandi Apr 06 '21 at 16:32
  • So you actually want to select data from both tables? Why then do you want to join one particular A row to one particular B row? What except for the transaction ID makes you decide which A row to join to which B row? – Thorsten Kettner Apr 06 '21 at 16:41

3 Answers3

4

Firstly, as you have been told multiple times in the comments, this is working exactly as you have written, and (more importantly) as intended. You have 2 rows in tableA and those 2 rows match 2 rows in your table tableB according to the ON clause. This means that each join operation, for the each of the rows in tableA, results in 2 rows as well; thus 4 rows (2 * 2 = 4).

Considering that your table, TableA only has one column then it seems that you should be cleaning up that data and deleting the duplicates. There are plenty of examples on how to do that already (example).

Perhaps the column you show us in TableA is one many, and thus instead you have a denormalisation issue, and instead there should be another table with the details of Id_trans and a PRIMARY KEY or UNIQUE CONSTRAINT/INDEX on it. Then you would join fron that table to TableB.

Finally, what you might be after is an EXISTS, which would look like this:

SELECT B.trans_id, B.[name]
FROM dbo.TableB B
WHERE EXISTS(SELECT 1
             FROM dbo.TableA A
             WHERE A.ID_Trans = B.trans_id); --Odd that it's called ID_Trans in one table, and Trans_ID in another
Thom A
  • 88,727
  • 11
  • 45
  • 75
1

As the comments mentioned your query does exactly what you asked it to do but I think you wanted something like:

select a.trans_id, a.name, b.name 
from tableA a 
inner join tableB b on a.trans_id = b.trans_id
group by a.trans_id, a.name, b.name
Lyrha
  • 11
  • 1
0

Since there are two rows in both table with same ID join will make them four. You can use distinct to remove duplicates:

select distinct a.trans_id, name 
from tableA a 
inner join tableB b 
   on a.id_trans = b.trans_id

But I would suggest to use exists:

select trans_id, name 
from tableB b
exists (select 1 from tableA a where a.trans_id=b.trans_id)
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26