Count Increased after Left join

[Bharathi_99]

Hello all,
I have two tables

Cntry, parts
In parts there are 30 columns and in cntry only 2 columns. I am doing Left join based on cntry_abb matching column in both tables Left tables is Parts and expaned the column Cntry_desc into parts table after join
I have 3.78M records in left table but after join it became 3.84, 8K difference  I am getting. 

How to get rid of this count increase issue?

Please Help

Thanks!!

[parry2k · Accepted solution]

@Bharathi_99 if you are linking from cntry table to part tables it will increase the count since there are duplicate in part table,

but if you are doing from part table to cntry table then the count of parts table will remain the same. It is very basic thing, not sure what is the confusion here.

[parry2k]

@Bharathi_99 it will increase the count:

assuming this

cntry table has following records:

USA 

Canada

Product table has following records

USA

USA

Canada

Canada

and based on the above if you do a left join you will get 4 records because of duplicate in the Product table. 

[Bharathi_99]

For example

CNTRY TABLE:

CNTRY_ABB	CNTRY_NAME
AU	AUSTRALIA
SP	SPAIN
EG	EGYPT
CA	CANADA

PARTS TABLE:

CNTRT_ABB	PART NUM	ID
AU	1054	1
AU	1056	2
SP	1057	3
EG	1057	3
CA	1053	4
CA	1052	4

If I am expanding only CNTRY_NAME Column into Parts table for CNTRY_ABB

How the values counts are increasing then...

[parry2k]

@Bharathi_99 you have probably duplicate cntry_abb in parts tables.

[Bharathi_99]

Hi @parry2k , I will be having duplicates too, but I don't know how count increased and why...