LEFT JOIN Woes

[Don]

The way I understand it, a LEFT JOIN uses all the records in the "left"
table, but only those record in "right" table that link to the left table.
So if I have something like:

.....
FROM tblPosition AS P LEFT JOIN tblPerson AS R ON P.PositionNumber =
R.PositionNumber
....

the result of the query should have the same number of records as in
tblPosition. (For the sake of this example, say there are 10 positions and
8 persons.)

Now, my problem comes when I add my WHERE clause:

WHERE P.PositionLocation IN ( strPositionFundingStatus )
AND R.PersonStatus In ( strProgramStatusCSV )

' strPositionLocation' has a list of sites
'strPersonStatus is either "Full", "Part", or "Full, Part"

If I limit the WHERE clause to

WHERE P.PositionLocation IN ( strPositionFundingStatus )

the query returns 10 records. However, if I include the the field from the
"right" table, the query 'ignore' the LEFT join part and returns only 8
records.

(Basically, the query reports who is on what position, and some positions
are open. We want to show the open positions.)

Any ideas will be greatly appreciated!

Thanks!

Don

 

[Don]

(Sorry about that! Too much cutting and pasting!)


The way I understand it, a LEFT JOIN uses all the records in the "left"
table, but only those record in "right" table that link to the left table.
So if I have something like:

....
FROM tblPosition AS P LEFT JOIN tblPerson AS R ON P.PositionNumber =
R.PositionNumber
...

the result of the query should have the same number of records as in
tblPosition. (For the sake of this example, say there are 10 positions and
8 persons.)

Now, my problem comes when I add my WHERE clause:

WHERE P.PositionLocation IN ( strPositionLocation)
AND R.PersonStatus In (strPersonStatus)

' strPositionLocation' has a list of sites
'strPersonStatus is either "Full", "Part", or "Full, Part"

If I limit the WHERE clause to

WHERE P.PositionLocation IN (strPositionLocation)

the query returns 10 records. However, if I include the the field from the
"right" table, the query 'ignore' the LEFT join part and returns only 8
records.

(Basically, the query reports who is on what position, and some positions
are open. We want to show the open positions.)

Any ideas will be greatly appreciated!

Thanks!

Don

 

[John Spencer (MVP)]

Try searching for the field being NULL also.

WHERE R.PersonStatus = strPersonStatus AND
(R.PositionLocation IS NULL
OR R.PositionLocation IN (...) )

 

[Don]

John,

Bingo!

Would have never thought of that!!

Thanks!

Don

 

[Per Larsen]

The way I understand it, a LEFT JOIN uses all the records in the "left"
table, but only those record in "right" table that link to the left table.
So if I have something like:

....
FROM tblPosition AS P LEFT JOIN tblPerson AS R ON P.PositionNumber =
R.PositionNumber
...

the result of the query should have the same number of records as in
tblPosition. (For the sake of this example, say there are 10 positions and
8 persons.)

This is generally not the case. If all records in the 'right' table were related to *only* one record in the 'left' table, you would have this record from the 'left' table repeated for each occurence in the 'right' table, and only a single record from each of all the others, e.g.:

10 records of different kind of fruits in the 'left' table
5 records of sale of the same kind of fruit

would give you 14 records, 9 with fruits with no sale, 5 with the one type sold.

Hth
PerL

 

[Don]

Per,

Good point!

Got a little wrapped around the specifics of my problem.

Thanks!

Don

This is generally not the case. If all records in the 'right' table were

related to *only* one record in the 'left' table, you would have this record
from the 'left' table repeated for each occurence in the 'right' table, and
only a single record from each of all the others, e.g.: