Inner Join, missing records

I

Iyigun Cevik

I have a simple inner join query like:

SELECT Home.ID AS ID, Home.ParselNo, Person.ID AS PersonID, Person.Name,
Person.Surname, Person.HomeID FROM Ev INNER JOIN Person ON Home.ID =
Person.HomeID

In this query there's records from table "Ev" in which there's at least one
corresponding record from "Kisi" table. If there's no corresponding records
in "Kisi" table, records from "Ev" table doesn't return. But i need them
also. In this case i'm trying to list all houses with people inside them,
but i need also empty houses.

I made a relation and choose in "join type" that it takes all records from
Home table.

I'm sorry for such a simple question, but i couldn't figure it out.
Iyigun Cevik
 
A

Allen Browne

You need an outer join.

Open your query in design view.
Double-click the line joining the 2 tables.
Choose the option:
All records from Ev, and any matches from Kisi.
 
V

Van T. Dinh

Use Left Outer Join:

SELECT Home.ID AS ID, Home.ParselNo, Person.ID AS PersonID, Person.Name,
Person.Surname, Person.HomeID
FROM Ev LEFT JOIN Person ON Home.ID = Person.HomeID
 
I

Iyigun Cevik

Thank you very much, I used exactly that and it worked. I had some missing
knowledge about outer join.
 
Top