What's wrong with this JOIN?

  • Thread starter Thread starter Helmar Herman
  • Start date Start date
H

Helmar Herman

I have 3 tables, Cases, Evidence, and EvidenceHistory.

I want to return a recordset all EvidenceHistory of All Evidence of
Case number 1.

This SQL statement has a problem.

SELECT Cases.*, Evidence.*, EvidenceHistory.*
FROM (EvidenceHistory INNER JOIN Evidence ON
EvidenceHistory.EvidenceID = Evidence.ID) INNER JOIN Cases ON
Evidence.CaseID = Cases.ID WHERE Cases.ID = 1

The problem is that the query does not return any records for Evidence
records that have no associated EvidenceHistory records.

For case 1, there are:

3 Evidence records, ID=1, ID=2, and ID=3
2 EvidenceHistory records, both pointing to Evidence ID 2.

The query above only returns 2 records:

Evidence 2, EvidenceHistory 1
Evidence 2, EvidenceHistory 2


Rather than the expected 4:

Evidence 1
Evidence 2, EvidenceHistory 1
Evidence 2, EvidenceHistory 2
Evidence 3

Can anyone help?
 
Back
Top