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>