SQL for joining two tables

[raysmith]

The environment for this question is based on Microsoft Access example tables Houshold and Members. I'm trying to create a query that gathers all Houshold table entries plus entries corresponding to all member table entries.
Attempts:
SELECT * FROM Household,Members
WHERE (Members.AddressID=Household.AddressID)

Results:
Lacks Household entries which do not correspond to any Members entries e.g., Stores or commercial entries.

SELECT * FROM Household,Members
WHERE (Members.AddressID=Household.AddressID)
OR ((Household.AddressID<>NULL) AND (MembersAddressID=NULL))
Results:
Lacks Household entries which do not correspond to any Members entries

Any ideas?

Regards,
Ray Smith

 

[Nouba]

Hi,

you could use

SELECT
  H.*
, M.*
FROM Household AS H
  LEFT JOIN Members AS M ON
    H.AddressID = M.AddressID

Happy New Year
Nouba

 

[Pat Hartman]

I see that you are using the old SQL join syntax. Although you can create joins with that syntax in Access, the resulting recordsets will NEVER be updateable because the recordsets are actually cartesian products that have been filtered. Switch to the new join syntax which indicates the join type specifically - Inner, Left, Right. Recordsets created with the specific join syntax will almost always be updateable.

Use the QBE grid to build your joins. Double click on the center of the join line to change the join type.