>>>I need to list all fields in DayTrans and link to Customer only to get
>>>the Address. Am I right to code as:
>>>
>>>SELECT DayTrans.CustCode, DayTrans.CustName, Customer.Address
>>>FROM DayTrans INNER JOIN Customer
>>>ON DayTrans.CustCode = Customer.CustCode
>>
>>Since you need all field from one of the table, you could also write:
>>SELECT DayTrans.*, Customer.Address
>>FROM DayTrans INNER JOIN Customer
>>ON DayTrans.CustCode = Customer.CustCode
>
>Am I right to say that the SQL Statement will return all rows from
>the DayTrans table and cross reference (using the CustCode key) to
>Customer and return only the Address if found? Thanks.
The INNER query returns only rows that have a match on both sides of the JOIN.
You can use a OUTER join to return all rows from one side (LEFT or RIGHT) and matching rows from the otherside.
SELECT DayTrans.*, Customer.Address
FROM DayTrans LEFT OUTER JOIN Customer
ON DayTrans.CustCode = Customer.CustCode
For those record of DayTrans that don't have matching CustCode in Customer, NULL will be placed in Address.
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com