<shaun thornburgh wrote>
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>
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]
