Sub-Select?

[email protected] · October 23rd, 2004, 08:15 PM

Hello Everyone and thanks for your help in advance. I am working on a query that selects all records from a patient release database, i.e.

Select Distinct MRNumber from tblReleases Where LastName Like 'R%'

Which returns a subset of records that are in the tblRelease table. I then want to see how many visits these patients have had, something like

Select * from tblPatientVisits Where MRNumber = (Select Distinct MRNumber from tblReleases Where LastName Like 'R%')

However this syntax doesn't work. I have also tried Where MRNumber In but that doesn't return the coreect records. Any help on this issue would be greatly appreciated.

Justin Cave · October 24th, 2004, 01:19 AM

I believe that you would want

Code:

SELECT *
  FROM tblPatientVisits
 WHERE MRNumber = (SELECT DISTINCT MRNumber
                     FROM tblReleases 
                    WHERE LastName LIKE 'R%')

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

joefawcett · October 24th, 2004, 03:15 AM

You can either use IN:

Code:

SELECT * FROM tblPatientVisits 
WHERE MRNumber IN 
(SELECT DISTINCT MRNumber 
FROM tblReleases
WHERE LastName LIKE '%R')

If you've tried that and it doewsn't work what sort of record is brought back that fails the criterion?
Or I think a better way is to use a join:

Code:

SELECT PV.*
FROM
tblReleases R INNER JOIN tblPatientVisits PV
ON R.MRNumber = PV.MRNumber
WHERE R.LastName LIKE 'R%'

--

Joe

[email protected] · October 24th, 2004, 07:45 AM

Thanks for the help. The IN clasue worked fine.