SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Whats wrong with this query?

    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.

    Code:
        
    $$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

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,087
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Code sql:
    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.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Code sql:
    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:

    Code:
    $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 ?

  4. #4
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,087
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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:

    Code:
    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:

    Code:
    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
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    another way...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much guys,
    very useful post. i have lot to take from this thread!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •