Access - Outer Join returns more records than original table

Please offer why in the following scenario

I have two tables - Table1 and Table2. Table1 originally has 100 records. However, when Ieft outer join with Table2 - this number increases to 110.

What I am trying to understand - is that if I am requesting to only return all in Table1, and those that match in Table1 and Table2 - the number should remain at 100 - since this was the original record count for Table1.

Thank you.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

exp vgAuthor Commented:
I should also add that this happens when I do not even include any fields from Table2 into the query grid.

Thanks.
arnoldCommented:
That is what the outer join does.

It matches your request, because you choose not to display columns from table2  it does not mean the columns you

Do you have rows that have null,empty values in the response and that is your concern/reason for asking?
exp vgAuthor Commented:
Let me please reword this.

Why is it that the original table has 100 records, But when outer joined - the extra 10 rows appears. to indicate 110 rows.

Especially since i have not included any Table2 fields in the design grid - but un design view I have the outer join in place, I am expecting that the generated output would still be 100.

Please advise.

Thank you.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Rey Obrero (Capricorn1)Commented:
how many records are there in table2?

how many of these records from table2 have multiple values in the field where you created the Left join?
Rey Obrero (Capricorn1)Commented:
rephrasing the q

how many of these records from table2 have duplicated values in the field where you created the Left join?
exp vgAuthor Commented:
I will check on this - so if I am gathering this correctly, even though I have yet to add the fields from Table2 into the grid - if there  are dups on the joined fields - this will increase the number in Table1.

Please confirm.
arnoldCommented:
Yes,
the selection is records meeting your criteria without regard whether you decide to display the column that differe within the dataset.
Without group by, all items you want from the records meeting your criteria will be returned.
arnoldCommented:
Rey,
None have duplicated
The query is
Select table1.column1, table1.column2, table1.column3 from table1 outer join table 2 .....

Table1 having 20 rows and table2 having 100
The return data  could be thirty rows.
arnoldCommented:
An Example is depicted in the following link.
http://infogoal.com/sql/sql-outer-join.htm

Table1 having 4 rows, after the query the response is 8 rows.
If you exclude the table 2 column
You will see a pair of rows is duplicated with the same data because while you chose not to display, the data is in the record set.
exp vgAuthor Commented:
I need to check on the duplicates in Table1 and Table2. Please give me a bit.

Thanks.
Rey Obrero (Capricorn1)Commented:
@arnold,

re: your post at http:#a40978373 

how did you know that there are no duplicates?
exp vgAuthor Commented:
I checked - and in table 2 there are name duplicates - but all unique in table 1. Is this where the addition of 10 rows is coming from.

I know this is tedious - but I really want to understand it all.

Thanks.
PatHartmanCommented:
When you join two tables using a left join you get  one row for each row in the "left" table and one row for each match in the right table.

tblLeft
Sam
Mary
Joe
Suzi

tblRight
Sam, Algebra
Sam, English
Sam, Biology
Mary, Physics
Joe, Algebra
Joe, Chemistry

The result set is
Sam, Algebra
Sam, English
Sam, Biology
Mary, Physics
Joe, Algebra
Joe, Chemistry
Suzi, blank

You get all the matches even if you are not selecting columns from the right-side table to display.  In that case it would look like:
Sam
Sam
Sam
Mary
Joe
Joe
Suzi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
there are no duplicates
table1
column1,column2
1,data
2,info
3,name

table2
column1,column3,column5
1,1,1
1,1,2
1,1,3
2,1,1
2,1,2
2,1,3
select table1.column1,table1.column2 from table1 outer join table2 on table1.column1=table2.column1

the results will be
1,data
1,data
1,data
Rey Obrero (Capricorn1)Commented:
@arnold,

what do you call the values in column1 of table2,  three 1, three 2 ?
exp vgAuthor Commented:
Thank you everyone - this is making sense to me now.
arnoldCommented:
Rey,

It was for illustration purpose, there was no need to add an identity column on table2, it could be a transnational table, relationship table. column4 and column5 are data points either conveying some information or are references to other tables that have this information.
I used column1 for both to visually indicate that they are related....
Rey Obrero (Capricorn1)Commented:
whether for illustration or whatever purpose, similar values are called duplicates.

also there is no outer join in access, it is right outer or left outer
arnoldCommented:
Rey, not sure I understand where duplicates come into play.
the rows while reporting the same information because of the request are not duplicates of the recordset.

My access familiarity is ....
Was addressing the question which applies in a similar matter whether it is access or MSsql,mysql, oracle...
Rey Obrero (Capricorn1)Commented:
@arnold
<Rey, not sure I understand where duplicates come into play.>

read again the post from the top....
PatHartmanCommented:
Access works the same way that all the other relational databases work.  Although Access is somewhat behind the times in the SQL features it implements, it is a fully relational database and any query that works in Jet/ACE will return the same result set in any other relational database.  The only exception I am aware of is how a RDBMS handles nulls.  That appears to vary.
arnoldCommented:
Rey,
I believe my responses addressed the question. The asker only selected columns from Table 1 while using an outer join to table 2 without specifying columns from table2 that should be displayed.


Question Subject/topic- "Access - outer join returns more records than original table"
Detail - "Access table 1 outer join table 2 without specifying columns from table 2)

The response reflecting the same information for the requested columns, but they are not duplicates in the recordset.

lets take 10  cards from one deck the the same 10 from another decks for a total of 20 both decs have identical face images. shuffled.
person A is directed to turn cards based on number/suite
There will be six cards facing up. While appearing duplicative, they are not as three came from one deck and the others came from another.

If you could clarify where I went wrong, .............
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.