Hi,
I have a problem with a SELECT query. I have a users table and each user can be a Rep, Nurse or Administrator defined by User_Type. Each Rep has two Nurses allocated to them and the User_ID of the nurse is held in column Rep_Nurse_1 and Rep_Nurse_2. I am trying to perform a query that returns the two nurses details based on the User_ID of the Rep:
SELECT U1.*, U2.* FROM Users U1, Users U2, Users R WHERE R.User_ID = 'x' AND U1.User_ID = R.Rep_Nurse_1 AND U2.User_ID = R.Rep_Nurse_2
However this returns just the first nurse allocated to that rep.
I have listed my User table defination below and would be most grateful for anyones advice here.
<html>
<head>
<title>query data</title>
<style type="text/css">
<!--
.normal { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 12px; font-weight: normal; color: #000000}
.medium { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 15px; font-weight: bold; color: #000000; text-decoration: none}
--></style>
</head>
<h3>query result ( 15 records )</h3><table border=1>
<tr>
<td bgcolor=silver class='medium'>Field</td><td bgcolor=silver class='medium'>Type</td><td bgcolor=silver class='medium'>Null</td><td bgcolor=silver class='medium'>Key</td><td bgcolor=silver class='medium'>Default</td><td bgcolor=silver class='medium'>Extra</td></tr>
<tr>
<td class='normal' valign='top'>User_ID</td><td class='normal' valign='top'>int(11)</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>PRI</td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'>auto_increment</td></tr><tr>
<td class='normal' valign='top'>Client_ID</td><td class='normal' valign='top'>int(3)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Username</td><td class='normal' valign='top'>varchar(40)</td><td class='normal' valign='top'> </td><td class='normal' valign='top'> </td><td class='normal' valign='top'> </td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Password</td><td class='normal' valign='top'>varchar(20)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Name</td><td class='normal' valign='top'>varchar(100)</td><td class='normal' valign='top'> </td><td class='normal' valign='top'> </td><td class='normal' valign='top'> </td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Type</td><td class='normal' valign='top'>varchar(20)</td><td class='normal' valign='top'> </td><td class='normal' valign='top'> </td><td class='normal' valign='top'>Nurse</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Email</td><td class='normal' valign='top'>varchar(100)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Manager_Email</td><td class='normal' valign='top'>varchar(100)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Manager_Email_2</td><td class='normal' valign='top'>varchar(100)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Manager_Email_3</td><td class='normal' valign='top'>varchar(100)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Location</td><td class='normal' valign='top'>varchar(40)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>User_Administrator</td><td class='normal' valign='top'>int(2)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>Mobile_Number</td><td class='normal' valign='top'>varchar(20)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>Rep_Nurse_1</td><td class='normal' valign='top'>int(11)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr><tr>
<td class='normal' valign='top'>Rep_Nurse_2</td><td class='normal' valign='top'>int(11)</td><td class='normal' valign='top'>YES</td><td class='normal' valign='top'> </td><td class='normal' valign='top'>(NULL)</td><td class='normal' valign='top'> </td></tr></table>
</body></html>
_________________________________________________________________
Express yourself with cool emoticons - download MSN Messenger today! http://www.msn.co.uk/messenger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
