SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dont show if same

    Hi guys


    What I want to do is not show the rows where the id is the same as job_id in the other table. Here is my code but it isnt working

    PHP Code:
    if (!empty($applications)) {

    $checkapplications =" select * from applications";


    $applicationsresult mysql_query ($checkapplications);

      
       while (
    $row mysql_fetch_array($applicationsresult)){

     
     
    $applicationid$row["job_id"];


    $query .= " AND id !='$applicationid' OR id !='$applicationid' ";


    }




    this is what is echoed out

    SELECT *, date_format(startdate,'%d/%m/%Y') startdate FROM Jobs WHERE 1=1 AND id='' OR id !='4' OR id !='4' OR id !='5' OR id !='5' OR id !='4' OR id !='4' OR id !='3' OR id !='3' AND sitesystem ='0' ORDER BY ''

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Try this:
    Code sql:
    SELECT DISTINCT Jobs.*, date_format(Jobs.startdate, '%d/%m/%Y') AS startdate FROM Jobs, applications WHERE Jobs.id != applications.job_id AND Jobs.sitesystem=0 GROUP BY Jobs.id
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the reply, have done that but it shows all fields and doesnt remove the ones that match

    Jobs.id != applications.job_id

  4. #4
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code sql:
    SELECT jobs.* 
          , date_format(startdate,'%d/%m/%Y') startdate
    FROM jobs
    LEFT JOIN applications ON jobs.id = applications.job_id
    WHERE applications.job_id = NULL
    Saul

  5. #5
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    shouldnt there be a ! before the =?

  6. #6
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Nope,

    Quote Originally Posted by MySQL Manual
    If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table
    Saul

  7. #7
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it displays no results

    SELECT Jobs.* , date_format(startdate,'%d/%m/%Y') startdate FROM Jobs LEFT JOIN applications ON Jobs.id = applications.job_id WHERE applications.job_id = NULL AND sitesystem ='0' GROUP BY Jobs.id ORDER BY ''

  8. #8
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Which means there are no such rows in jobs table that have no counterparts in applications table. Is that possible?
    Saul

  9. #9
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    applications

    job_id
    4
    5
    4
    3


    Jobs

    id

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

  10. #10
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Can you run a simple query:
    Code sql:
    SELECT jobs.* 
    FROM jobs
    LEFT JOIN applications ON jobs.id = applications.job_id
    WHERE applications.job_id IS NULL
    Saul

  11. #11
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what should i be looking for

    if i do this

    SELECT Jobs.* , date_format(startdate,'%d/%m/%Y') startdate FROM Jobs LEFT JOIN applications ON Jobs.id = applications.job_id WHERE applications.job_id = Jobs.id AND sitesystem ='0' GROUP BY Jobs.id ORDER BY ''
    it shows current jobs with applications

    all i want is the opposite (jobs without applications)

  12. #12
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes, I know that, and that's why the query should work. Can you run the query with minimal restrictions in console or phpmyadmin?

    BTW, you don't need the GROUP BY clause there at all, when trying to select jobs with no applications.

    Please run this and see how it works:
    Code sql:
    SELECT jobs.* 
    FROM jobs
    LEFT JOIN applications ON jobs.id = applications.job_id
    WHERE applications.job_id IS NULL
    Saul

  13. #13
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    works fine, could it be anything else?

  14. #14
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    And if you add your AND sitesystem='0' ?
    Saul

  15. #15
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    SELECT jobs.*
    , date_format(startdate,'%d/%m/%Y') AS startdate

    ??
    Ian Anderson
    www.siteguru.co.uk

  16. #16
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    ^ AS is optional
    Saul


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
  •