Inner Join, missing records

  • Thread starter Thread starter Iyigun Cevik
  • Start date Start date
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
 
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.
 
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
 
Thank you very much, I used exactly that and it worked. I had some missing
knowledge about outer join.
 
Back
Top