MySQL QUERY HELP

[tgreen]

I need assistance building this MySQL query. Basically I am trying to eliminate users with a certain ID_NUM while grouping the USR_ID together.

Here is the sample table:

usr_id id_num

USR1 101
USR2 101
USR3 101
USR1 201
USR2 203
USR3 204

Here is the query that does not work:

SELECT usr_id FROM table WHERE id_num = 101 AND id_num != 201 GROUP BY usr_id

I am trying to get back the following:

usr_id id_num

USR2 101
USR3 101

[ultraslacker]

Is that your real query? "id_num = 101 AND id_num != 201" is not logical.

[drawmack]

Originally posted by tgreen

usr_id id_num

USR1 101
USR2 101
USR3 101
USR1 201
USR2 203
USR3 204

This needs to be done either with multiple queries or with a join. I'm not the most proficient with joins so I'll show it in multiple queries.

$match1 = array();
$sql = "SELECT usr_id FROM users WHERE id_num != 101";
$sql2 = "SELECT usr_id FROM users WHERE id_num = 101";
$query = mysql_query($sql);
for($i=0;$i<mysql_num_rows($query);$i++) {
  array_push($match1,$query,$i,0);
}
$sql2 .= " AND usr_id NOT IN ('" . implode("','",$match1) . "')";
$query = mysql_query($sql);

[tgreen]

I know... it's not logical at all. That is why I am having such a problem. Can someone tell me if I can do this with a join statement? (Or at least without multiple queries?)

[ultraslacker]

To select the rows for id_num 101 while exluding rows with usr_ids shared with id_num 201:

SELECT t1.usr_id, t1.id_num
FROM test t1
LEFT OUTER JOIN test t2 ON (t1.usr_id = t2.usr_id AND t2.id_num = 201) WHERE t2.usr_id IS NULL AND t1.id_num = 101
GROUP BY t1.usr_id

[tgreen]

Thanks Alot!

[tgreen]

ultraslacker - WORKS GREAT!