SQLTeam.com | Weblogs | Forums

One-To-Many Is Creating a Problem


#1

Here is my problem. I created a query as a datasource for a web page. I have three tables:
tblFactReferrals has multiple records for the same patient
tblDimPatients has one record per patient
tblDropOptions is used to establish a sort order for tblDimPatients.GraduatingYear

When I open the web page, the result is that there is a record for every record in tblFactReferrals. I only need the oldest record from that table. I have included the layout of the query. Can someone help?

Thank you.

SELECT tblDimPatients.LastName AS [Last Name], tblDimPatients.FirstName AS [First Name], tblDimPatients.MInitial AS MI, tblFactReferrals.ReferralDate AS [Referral Date],
tblDimPatients.GraduatingYear AS [Graduating Year], tblDimPatients.PatientID
FROM tblDropOptions RIGHT OUTER JOIN
tblDimPatients ON tblDropOptions.doOption = tblDimPatients.GraduatingYear LEFT OUTER JOIN
tblFactReferrals ON tblDimPatients.PatientID = tblFactReferrals.PatientID
WHERE (tblDimPatients.Status = 'Referral')
ORDER BY tblDropOptions.doOrder, [Referral Date] DESC


#2

SELECT tblDimPatients.LastName AS [Last Name],
tblDimPatients.FirstName AS [First Name],
tblDimPatients.MInitial AS MI,
MaxRefDate.[Referral Date],
tblDimPatients.GraduatingYear AS [Graduating Year],
tblDimPatients.PatientID
FROM tblDropOptions
RIGHT OUTER JOIN tblDimPatients ON tblDropOptions.doOption = tblDimPatients.GraduatingYear
LEFT OUTER JOIN (SELECT PatientID, MAX([ReferralDate]) AS [Referral Date] FROM tblFactReferrals GROUP BY PatientID )MaxRefDate ON tblDimPatients.PatientID = MaxRefDate.PatientID
WHERE(tblDimPatients.Status = 'Referral')
ORDER BY tblDropOptions.doOrder;


#3

I tried this and got different results but not the one I needed. I also changed Max to Min, which is what I need. The result is that it is sorting on the LastName DESC, and it is still pulling all the records from tblFactReferrals. Thanks. Can you try again?


#4

SELECT tblDimPatients.LastName AS [Last Name],
tblDimPatients.FirstName AS [First Name],
tblDimPatients.MInitial AS MI,
(
SELECT MAX( [ReferralDate] ) AS [Referral Date]
FROM tblFactReferrals
WHERE PatientID = tblDimPatients.PatientID
GROUP BY PatientID ) [Referral Date],
tblDimPatients.GraduatingYear AS [Graduating Year],
tblDimPatients.PatientID
FROM tblDropOptions
RIGHT OUTER JOIN tblDimPatients ON tblDropOptions.doOption = tblDimPatients.GraduatingYear
WHERE(tblDimPatients.Status = 'Referral')
ORDER BY tblDropOptions.doOrder;