MS Access join properties
- StarPhoenix
- B.Soc.Sci, M.SocSci [UPCF]
- Posts: 17634
- Joined: 11 Dec 2003, 02:00
- Processor: Core i5 3470
- Motherboard: Gigabyte G1 Sniper Z77
- Graphics card: nVidia GeForce GTX 1060
- Memory: 8Gb DDR3 1600
- Location: East London
- Contact:
MS Access join properties
I have been trying to learn more about databases today, but have come unstuck with Join Properties:
I cannot get my mind around the description Microsoft gives, namely:
1. Only include rows where joined fields from both tables are equal.
2. Include all records from "Pin details" and only those records from "History" where the joined fields are equal.
3. Include all records from "History" and only those records from "Pins" where the joined fields are equal.
Huh?
What does it mean for both fields to be equal?
If all records from a given table are given, does that mean that the database is going to throw masses of information at me?
Why is a distinction made between rows and records? I thought they were the same thing.
Thank you in advance.
"Humankind cannot bear very much reality." T.S. Elliot
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: MS Access join properties
Ok - see it like this.
You have a video store that has one table that shows all their clients
They have another table that shows all the videos that have been booked out.
All the tables have been related so that the customer and Video table are related
In a query -- you can join the tables using the options you mentioned. This is what will happen.
Customer ------Video bookings
1. Only those customers that have taken out videos will be visible. That means that the customer code must exist in both tables.
Customer ------>Video bookings
2. All the customers will be visible and only the videos will be reflected that have been taken out. Customers who have not taken out videos will have blank lines where the video table is concerned.
Customer< ------Video bookings
3. This shows all Videos that were taken out, regarless if you have the customer code or not. If videos were booked out without a client code, you would see blank lines next to the video data.
I hope this helps.
You have a video store that has one table that shows all their clients
They have another table that shows all the videos that have been booked out.
All the tables have been related so that the customer and Video table are related
In a query -- you can join the tables using the options you mentioned. This is what will happen.
Customer ------Video bookings
1. Only those customers that have taken out videos will be visible. That means that the customer code must exist in both tables.
Customer ------>Video bookings
2. All the customers will be visible and only the videos will be reflected that have been taken out. Customers who have not taken out videos will have blank lines where the video table is concerned.
Customer< ------Video bookings
3. This shows all Videos that were taken out, regarless if you have the customer code or not. If videos were booked out without a client code, you would see blank lines next to the video data.
I hope this helps.
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: MS Access join properties
1) i have 2 tables: InvoiceHeader and InvoiceLine. in a relational database, these tables can be mapped using a set of common FIELDS, (in this case the most obvious would be InvoiceNumber, possibly even a document type as well).StarPhoenix wrote:What does it mean for both fields to be equal?
So we can JOIN the results of these two tables in a single query
Code: Select all
SELECT
HED.InvoiceNumber,
HED.CustNumber,
HED.Address,
LIN.ProductNumber,
LIN.PickedQty,
LIN.UnitPrice,
LIN.CostPrice
FROM
MYCAT..InvoiceHeader HED WITH(NOLOCK) LEFT JOIN
MYCAT..InvoiceLine LIN WITH(NOLOCK) ON
(LIN.DOCTYPE=HED.DOCTYPE) AND
(LIN.InvoiceNumber=HED.InvoiceNumber)
You can filter these results using a WHERE clause...
Code: Select all
SELECT
HED.InvoiceNumber,
HED.CustNumber,
HED.Address,
LIN.ProductNumber,
LIN.PickedQty,
LIN.UnitPrice,
LIN.CostPrice
FROM
MYCAT..InvoiceHeader HED WITH(NOLOCK) LEFT JOIN
MYCAT..InvoiceLine LIN WITH(NOLOCK) ON
(LIN.DOCTYPE=HED.DOCTYPE) AND
(LIN.InvoiceNumber=HED.InvoiceNumber)
WHERE
HED.InvoiceDate>'2010-11-01'
So, the FIELD they refer to is the one:
- :- used to describe the RELATIONSHIP between the tables
:- used to filter, (WHERE), the results
:- used to GROUP results or
:- used to sort, (ORDER BY), results.
only if you play silly buggers and build queries designed to do so.. an example is:StarPhoenix wrote:If all records from a given table are given, does that mean that the database is going to throw masses of information at me?
Code: Select all
SELECT
*
FROM
MYCAT..InvoiceHeader
SQL supports wild cards...
- *==EVERYTHING
% is a simple regex card used in pattern matching...
Code: Select all
SELECT
*
FROM
MYCAT..InvoiceHeader
WHERE
InvoiceHeader.CustNumber LIKE '204%'
the JOIN you use depends on what you're after... you have 3 main options, but only 2 that are most commonly used..
- :- INNER - Ignore broken relationships..
:- OUTER - return everything form the primary table, and NULL on broken relationships...
:- FULL - returns the entire bucket... intact relationships will be mapped correctly, but broken ones - (from BOTH tables) - will be there as well... cluttered to the gills with SQL nulls.... on reflection i can't remember access supporting full joins... (Tribble?)
the keyword JOIN is synonymous with INNER JOIN so you can use either...
OUTER, (LEFT or RIGHT) - All records from the primary table, (LEFT would be the table to the left of the JOIN keyword, RIGHT that to the right), and only those matching the relationship bounds - if the InvoiceLine table has no record of an InvoiceNumber in the InvoiceHeader table, the resultset will substitute SQL nulls.
the keywords LEFT or RIGHT signify an OUTER join so you can substitute LEFT JOIN or RIGHT JOIN...
<edit>
corrected for "access doesn't do that!"
</edit>
Most people would sooner die than think; in fact, they do so - Bertrand Russel
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: MS Access join properties
Not meaning to burst your puppy bubble - but that would be very confusing to someone who has only done a few hours of Access tables. I want him to love Access - not be terrified of it.
Birdie - puppy is 100% right but you don't need to get that complex this early in the game.
Birdie - puppy is 100% right but you don't need to get that complex this early in the game.