Re: Problem with a Select Query
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.*rse_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. --8< SNIP 8<--- Shaun, The problem is in your WHERE clause. You need an OR for the last AND (and a set of parentheses, too) SELECT U1.*, U2.* FROM Users U1, Users U2, Users R WHERE R.User_ID = 'x' AND ( U1.User_ID = R.Rep_Nurse_1 OR U2.User_ID = R.Rep_Nurse_2 ) Another way to write this is through JOINS SELECT U1.*, U2.* FROM Users R LEFT JOIN Users U1 ON U1.User_ID = R.Rep_Nurse_1 LEFT JOIN Users U2 ON U2.User_ID = R.Rep_Nurse_1 WHERE R.User_ID = 'x' Both of those will give you two sets of columns (2x the same column names in the same query).If you had a field called Name in your Users table, you would have two of them in your results. One way to get it down to a single set of columns would be to use a UNION query (depending on which version of MySQL you are running). The advantage to this is that if a Rep only has 1 nurse assigned to them, you only get one row of data and not a whole set of NULL values in the duplicate columns. SELECT U1.* FROM Users R INNER JOIN Users U1 ON U1.User_ID = R.Rep_Nurse_1 WHERE R.User_ID = 'x' UNION SELECT U2.* FROM Users R INNER JOIN Users U2 ON U2.User_ID = R.Rep_Nurse_1 WHERE R.User_ID = 'x' There is still another method that involves a lot of IF statements but since you didn't post the column names for your Users table, I can't work it up for you. Hope this helps! Shawn Green Database Administrator Unimin Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with a Select Query
SELECT user_id, rep_nurse_1, rep_nurse_2 FROM Users WHERE User_ID = 'x' > 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with a Select Query
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.
query data
<br>
<!--<br>
.normal { font-family: Verdana, Arial, Helvetica, sans-serif; font-size:
12px; font-weight: normal; color: #00}<br>
.medium { font-family: Verdana, Arial, Helvetica, sans-serif; font-size:
15px; font-weight: bold; color: #00; text-decoration: none}<br>
-->
query result ( 15 records )
FieldTypeNullKeyDefaultExtra
User_IDint(11) PRI(NULL)auto_increment
Client_IDint(3)YES (NULL)
User_Usernamevarchar(40)
User_Passwordvarchar(20)YES (NULL)
User_Namevarchar(100)
User_Typevarchar(20) Nurse
User_Emailvarchar(100)YES (NULL)
User_Manager_Emailvarchar(100)YES (NULL)
User_Manager_Email_2varchar(100)YES (NULL)
User_Manager_Email_3varchar(100)YES (NULL)
User_Locationvarchar(40)YES (NULL)
User_Administratorint(2)YES (NULL)
Mobile_Numbervarchar(20)YES (NULL)
Rep_Nurse_1int(11)YES (NULL)
Rep_Nurse_2int(11)YES (NULL)
_
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]
