Whats wrong with this query?

[afridy]

Hai folks

Table candiates

cid | agency_code
+++++++++
1 | 3
2 | 3
3 | 2
4 | 1
5 | 3
6 | 4
7 | 4
8 | 1

Table approvals

cid
++++++++++
4
5
8

i want to list
* all the candiates belongs to agency code 3 and not found in approval tables.

    
$$agency_code='3'
$query="SELECT candidates.* from candidates, approvals" . 
				     " WHERE (candidates.cid <> approvals.cid) AND candidates.agency_code='$agency_code'";

the result should be
1| 3
2| 3

but i get strange result.

1|3
1|3
1|3
2|3
2|3
2|3
5|3
5|3
5|3

somthing like that

[ScallioXTX]

SELECT
   something
 , anything
 , just_not_the_dreaded_star
FROM
  candidates
WHERE
  agency_code = 3
  AND
  cid NOT IN
  (
    SELECT
       id
    FROM
       approvals
  )

Off Topic:


Also, you may want to have a look at parameter escaping to prevent SQL injection. Either use mysqli_real_escape_string if you're using MySQLi, or use prepared statements if you're using PDO.

[afridy]

SELECT
   something
 , anything
 , just_not_the_dreaded_star
FROM
  candidates
WHERE
  agency_code = 3
  AND
  cid NOT IN
  (
    SELECT
       id
    FROM
       approvals
  )

Off Topic:


Also, you may want to have a look at parameter escaping to prevent SQL injection. Either use mysqli_real_escape_string if you're using MySQLi, or use prepared statements if you're using PDO.

Great relief! works charm!!!

But i am confused. why my query was working correctly when i test for = ?
ex:

$agency_code='3'
$query="SELECT candidates.* from candidates, approvals" . 
				     " WHERE (candidates.cid = approvals.cid) AND candidates.agency_code='$agency_code'";

This produce correct results if i want to display all approved candaites. but when i change the = to <> strage results ?

[afridy]

Also, you may want to have a look at parameter escaping to prevent SQL injection. Either use mysqli_real_escape_string if you're using MySQLi, or use prepared statements if you're using PDO.

Thanks so much, ill chk about this

[ScallioXTX]

The way you posed your query you will get a Cartesian_product. That is to say, you will get a combination of all rows from the `candidates` table with all rows in the `approvals` table, like this:

candidates        | approvals
------------------------------
cid | agency_code | cid       
------------------|-----------
1   | 3           | 4
1   | 3           | 5
1   | 3           | 8
2   | 3           | 4
2   | 3           | 5
2   | 3           | 8
3   | 2           | 4
3   | 2           | 5
3   | 2           | 8
4   | 1           | 4
4   | 1           | 5
4   | 1           | 8
5   | 3           | 4
5   | 3           | 5
5   | 3           | 8
6   | 4           | 4
6   | 4           | 5
6   | 4           | 8
7   | 4           | 4
7   | 4           | 5
7   | 4           | 8
8   | 1           | 4
8   | 1           | 5
8   | 1           | 8

So when you query with = you will get all rows I've highlighted in red above, which is what you expected (even though it's not the correct way to achieve it, the correct way would be to use an INNER JOIN). No when you change = to <> you will get all the rows for which the agency_code is 3 and the first column of the table above isn't equal to the last column, which is pretty much most of the rows that have agency_code = 3.

To visualise, these are the rows you get back from that query:

candidates        | approvals
------------------------------
cid | agency_code | cid       
------------------|-----------
1   | 3           | 4
1   | 3           | 5
1   | 3           | 8
2   | 3           | 4
2   | 3           | 5
2   | 3           | 8
3   | 2           | 4
3   | 2           | 5
3   | 2           | 8
4   | 1           | 4
4   | 1           | 5
4   | 1           | 8
5   | 3           | 4
5   | 3           | 5
5   | 3           | 8
6   | 4           | 4
6   | 4           | 5
6   | 4           | 8
7   | 4           | 4
7   | 4           | 5
7   | 4           | 8
8   | 1           | 4
8   | 1           | 5
8   | 1           | 8

[r937]

another way...

SELECT something
     , anything
     , just_not_the_dreaded_select_star
  FROM candidates
LEFT OUTER
  JOIN approvals
    ON approvals.cid = candidates.cid  
 WHERE candidates.agency_code = 3
   AND approvals.cid IS NULL

[afridy]

Thanks so much guys,
very useful post. i have lot to take from this thread!!