[SOLVED] Need SQL help - Select with a "but" clause?

[benphp]

Sorry to keep bugging you guys, but I've been working on this for hours and can't come up with a satisfactory statement. I'm trying to get a list of names of people in "users" that have an rcount of 1 ONLY. So I want to exclude people who have an rcount of 2 or 3. So selecting all with rcount = 1 isn't good enough. I need to select all with rcount = 1, but only 1.

 

Any ideas?

 

"resp"

+-----+------+------+------+-------+---------+-------+--------+
| rid | pid  | qid  | uid  | score | ractive | shift | rcount |
+-----+------+------+------+-------+---------+-------+--------+
|   1 |    1 |    1 |  215 | 1     | NULL    | 4     |      1 |
|   2 |    1 |    2 |  215 | 2     | NULL    | 4     |      1 |
|   3 |    1 |    3 |  215 | 1     | NULL    | 4     |      2 |
|   4 |    1 |    4 |  215 | 2     | NULL    | 4     |      2 |
|   5 |    1 |   31 |  216 | 2     | NULL    | 4     |      1 |
|   6 |    1 |   13 |  216 | 2     | NULL    | 4     |      1 |
|   7 |    1 |    5 |  216 | 3     | NULL    | 4     |      2 |
|   8 |    1 |    6 |  216 | 1     | NULL    | 4     |      2 |
|   9 |    1 |    7 |  217 | 1     | NULL    | 4     |      1 |
|  10 |    1 |    8 |  217 | 1     | NULL    | 4     |      1 |

 

"users"

+-----+-------+----------+
| uid | first | last     |
+-----+-------+----------+
| 204 | Mark  | Addama    |
| 205 | Kevin | Barker   |
| 206 | Bill  | Barnwell |
| 207 | James | Bataki  |
| 208 | Barry | Buer    |

[revraz]

I don't get it, why isnt a WHERE rcount = 1 good enough?

 

So selecting all with rcount = 1 isn't good enough. I need to select all with rcount = 1, but only 1.

 

 

[benphp]

Solved it:

 

SELECT DISTINCT resp.rid, users.first, users.last, users.email 
FROM users RIGHT JOIN resp ON users.uid = resp.uid 
WHERE 1 = (SELECT MAX(resp2.rcount) FROM resp AS resp2 WHERE resp2.uid = $temp)  
AND resp.uid = $temp
ORDER BY users.last
LIMIT 1

 

revraz - it wouldn't work because it would return people who have both 1 and 2. I want to exclude people with both 1 and 2 and just return people with 1.