left join returns MORE rows than the left table

[dkiernan]

I have 2 tables; one field in Parts is a foreign key into Locations.

select Parts.Name, Parts.Description, Locations.Description
from Parts left join Locations on Parts.LocationID =
Locations.LocationID;

This returns MORE records than are in the Parts table to begin with.

I have 17,000 Parts records, and the query returns over 20,000 records.

My understanding is that a left join retuns all records in the left
table with matching records in the right table or blanks if there is no
matching record in the right table.

How am I ending up with *more* records?

Thanks.

 

[Rick Brandt]

I have 2 tables; one field in Parts is a foreign key into Locations.

select Parts.Name, Parts.Description, Locations.Description
from Parts left join Locations on Parts.LocationID =
Locations.LocationID;

This returns MORE records than are in the Parts table to begin with.

I have 17,000 Parts records, and the query returns over 20,000
records.

My understanding is that a left join retuns all records in the left
table with matching records in the right table or blanks if there is
no matching record in the right table.

How am I ending up with *more* records?

Thanks.

If a record in the left table has more than one match in the right table then
you will get multiple instances of THAT row in the output.

LEFT RIGHT
1 1
2 1
3 2
4 3

OUTPUT
1
1
2
3
4

 

[dkiernan]

ne'er mind...

It seems there are duplicates of the foreign key in the second table.
For example, Locations has two entries for "SID-C". Any Parts in
location "SID-C" were returning 2 rows in the left join.

Let there be light...

--David.

 

[dkiernan]

Yes. I was looking at it from the data viewpoint. There *shouldn't*
be more than one record in the right-side table with the same
LocationID. I thought it was the primary key, but it turns out not to
be a unique ID field in the right-side table.

Thanks for the quick response.

--David