List:General Discussion« Previous MessageNext Message »
From:william drescher Date:September 2 2009 11:13am
Subject: join to return first record for each patient number
View as plain text  
I have two tables:
PtActive
    ptNum  // the patient's number
    user   // the user who made this patient "active"
    expires// when the patient becomes inactive again
primary index: PtNum

PtName
   ptNum
   sequence
   lname
   fname
primary index: ptNum, sequence

The table PtName may have multiple rows with the same ptNum (if 
the patient changes his/her name.

I am going mildly nuts trying to devise a query that will 
retrieve only the lowest ptName (ie: their current name) for all 
active patients for this user.

in PHP
I tried:
$sql ="select PtName.ptNum, lname,  fname from PtName, PtActive 
where PtName.ptNum = PtActive.ptNum and PtActive.user = 
'$currentUser' 	order by PtName.ptNum, PtName.nameSequence ";

but this retrieves all names for this patient.

I tried:
$sql ="select distinct PtName.ptNum, lname,  fname from PtName, 
PtActive where PtName.ptNum = PtActive.ptNum and PtActive.user = 
'$currentUser' order by PtName.ptNum, PtName.nameSequence ";

but this retrieves all names for all active patients.

I tried a subquery
$sql ="select ptNum, lname,  fname from PtName where ptNum 
=(select ptNum from PtActive where PtActive.user = '$currentUser' 
limit 1)";

but this returns all the names for the first active patient.
If I remove the limit 1, it fails with the error message 
"Subquery returns more than 1 row"

Help !

bill


Thread
join to return first record for each patient numberwilliam drescher2 Sep