SQL returns only 35 records when it should return 50,000?

[ollyculverhouse]

Hi,

I have a query which basically pulls information from 3 different
tables depending on a persons ID number.
The three tables are:

individuals
specs
specialneeds

specs and specialneeds are linked to individuals in a one-to-one
relationship.

I want to get all information from specs and specialneeds as well as a
few details from individuals (name initials e.t.c)
In the specs and specialneeds tables there is a kep called 'indvID'
which links to the individuals ID.

Right, I have setup a query with the following:
SELECT specs.*, specialneeds.*, individuals.title,
individuals.initials, individuals.lastName
FROM (individuals INNER JOIN specs ON individuals.ID = specs.indvID)
INNER JOIN specialneeds ON individuals.ID = specialneeds.indvID;

This query only returns 35 results out of 50,000, (it should return all
50,000). I am assuming that the reason for this is because only 35
records have information in all three tables?

How would i change the SQL statement to include all details, blank or
not?

 

[Gary Walter]

I have a query which basically pulls information from 3 different
tables depending on a persons ID number.
The three tables are:

individuals
specs
specialneeds

specs and specialneeds are linked to individuals in a one-to-one
relationship.

I want to get all information from specs and specialneeds as well as a
few details from individuals (name initials e.t.c)
In the specs and specialneeds tables there is a kep called 'indvID'
which links to the individuals ID.

Right, I have setup a query with the following:
SELECT specs.*, specialneeds.*, individuals.title,
individuals.initials, individuals.lastName
FROM (individuals INNER JOIN specs ON individuals.ID = specs.indvID)
INNER JOIN specialneeds ON individuals.ID = specialneeds.indvID;

This query only returns 35 results out of 50,000, (it should return all
50,000). I am assuming that the reason for this is because only 35
records have information in all three tables?

How would i change the SQL statement to include all details, blank or
not?

If you are saying there are 50,000 individuals,
then you need to LEFT JOIN individuals to
the other 2 tables.

In query designer, right-mouse click on a join line,
choose "Properties"
then choose option to return all of individuals
and only matching other table.

 

[ollyculverhouse]

Thanks, I knew it was something simple

If you are saying there are 50,000 individuals,
then you need to LEFT JOIN individuals to
the other 2 tables.

In query designer, right-mouse click on a join line,
choose "Properties"
then choose option to return all of individuals
and only matching other table.