MS Access join properties

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
User avatar
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

Post by StarPhoenix »

:oops:

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? :scratch:

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
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: MS Access join properties

Post by rustypup »

StarPhoenix wrote:What does it mean for both fields to be equal?
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).

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) 
In this query, you will indeed get EVERYTHING... however, the InvoiceLine details will be mapped to their matching InvoiceHeader record...

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' 
This would filter the InvoiceHeader records to current documents... as we have opted to only include lines from InvoiceLine where records share both document type AND invoice number with the InvoiceHeader table, the records in InvoiceLine are also filtered... unless there is an InvoiceHeader record with no matching InvoiceNumber in the InvoiceLine table, (see joins below).

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.
RECORD and ROW are generally interchangeable, although row is more commonly used when referring to a resultset...
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?
only if you play silly buggers and build queries designed to do so.. an example is:

Code: Select all

SELECT 
    * 
FROM 
    MYCAT..InvoiceHeader 
would flood you with everything in InvoiceHeader......

SQL supports wild cards...
  • *==EVERYTHING
    % is a simple regex card used in pattern matching...
as an example...

Code: Select all

SELECT 
    * 
FROM 
    MYCAT..InvoiceHeader 
WHERE 
    InvoiceHeader.CustNumber LIKE '204%'
would return header records where CustNumber starts with "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?)
JOIN/INNER JOIN - Only records where the field matches on both sides of the join... if either table is missing a particular key, nothing will appear in the result set... ie,if the InvoiceLine table has no record of an InvoiceNumber in the InvoiceHeader table, the resultset won't substitute SQL nulls - it will simply skip over the record...

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
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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. :lol:

Birdie - puppy is 100% right but you don't need to get that complex this early in the game.
Image
Post Reply