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=arch...@jab.org
