join to return first record for each patient number

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org