• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Unwanted cartesian results (a chronic problem for me!)

I keep getting unwanted cartesian results in my join, no matter how long I work at this.  (Access SQL seems to give me a harder time than SQL Server.)

I want all records from the patient table and only one matching record each from the insurance table.  (There are multiple matches in the table.)
SELECT 
EpiscopalPatient.PatName, 
EpiscopalPatient.VisitNumber, EpiscopalPatient.MedRecordNumber,
EpiscopalInsurance.GuarName
 
FROM EpiscopalPatient 
LEFT JOIN EpiscopalInsurance 
ON EpiscopalPatient.MedRecordNumber = EpiscopalInsurance.MedicalRecordNumber

Open in new window

0
AmyFPA
Asked:
AmyFPA
1 Solution
 
TirocupidusCommented:
Does it matter which match it pulls from your insurance table?
0
 
MikeTooleCommented:
<There are multiple matches in the table>
Are you saying that  there can be more than one row in EpiscopalInsurance for a single MedicalRecordNumber?
If not then the inclusion of Visit Number in the Select list will result in one row for every PatName/Visit combination in EpiscopalPatient
0
 
Patrick MatthewsCommented:
SELECT
EpiscopalPatient.PatName,
EpiscopalPatient.VisitNumber, EpiscopalPatient.MedRecordNumber,
Max(EpiscopalInsurance.GuarName) AS Guardian
 
FROM EpiscopalPatient
LEFT JOIN EpiscopalInsurance
ON EpiscopalPatient.MedRecordNumber = EpiscopalInsurance.MedicalRecordNumber
GROUP BY EpiscopalPatient.PatName,
EpiscopalPatient.VisitNumber, EpiscopalPatient.MedRecordNumber
0
 
AmyFPAAuthor Commented:
You're wonderful!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now