understanding relationship JOIN TYPE dialog window 1

[vttech]

I have created two table in my database listed below

tblEmployee
EmployeeID{Primary Key, Auto Number}
FirstName
LastName
PositionTitle

tblWorkHistory
EmployeeID {Foreign Primary Key}
Organization {Primary Key}
Position
StartDate
EndDate

I am creating a one to many relationship with the tblEmployee EmployeeID and tblWorkHistory EmployeeID
because one Employee can have many past jobs

where I run in a cloud is that in access under the edit relationship dialog window it has an button called JOIN TYPE when I click on that button it open the Join Properties dialog window and presents me with three options

1. Only include rows where the joined fields from both tables are equal

2. Include all recoreds from the 'tblEmployee' and only those records from 'tblWorkHistory' where the joined fields are equal

3. Include all records from 'tblWorkHistory' and only those records from 'tblEmployee' where the joined fields are equal

Can someone explain what access is trying to ask me as it pertains to my example. I am new to access to a basic answer would help me with future relationship issues and a better understand of the concept


Thanks

 

[GHolden]

Hi,

Take the following tables:

EmployeeTable(A)
EmployeeID Name
1 John
2 Fred
3 David

OrganisationTable(B)
Organisation EmployeeID
1 1
2 1
3 3
4 4

Assuming the tables are joined by EmployeeID:

If you did a query with Only include rows where the joined fields from both tables are equal (tblEmployee INNER JOIN tblWorkHistory)

You would get the following:

A.EmployeeID A.Name B.Organisation B.EmployeeID
1 John 1 1
1 John 2 1
3 David 3 3

Only where EmployeeID appears in BOTH tables will records be returned (i.e. EmployeeID 2 is not shown as there is not a record in OrganisationTable).

If you did a query with Include all recoreds from the 'tblEmployee' and only those records from 'tblWorkHistory' where the joined fields are equal (tblEmployee LEFT join tblWorkHistory)

A.EmployeeID A.Name B.Organisation B.EmployeeID
1 John 1 1
1 John 2 1
2 Fred Null Null
3 David 3 3

This forces all the records from Employee to be returned regardless of whether there is a corresponding record in Organisation. Where no corresponding record exists nulls are returned for the Organisation fields.

If you did a query with Include all records from 'tblWorkHistory' and only those records from 'tblEmployee' where the joined fields are equal (tblWorkHistory LEFT join tblEmployee)

A.EmployeeID A.Name B.Organisation B.EmployeeID
1 John 1 1
1 John 2 1
3 David 3 3
Null Null 4 4

This gives you the opposite of the previous example. All records are returned form Organisation and where no corresponding record exists in Employee Nulls are returned.

Hope this clarifies things a bit?

There are two ways to write error-free programs; only the third one works.

 

[lespaul]

check out Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design