Dont show if same

[wazo]

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

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 ''

[Jake Arkinstall]

Try this:

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

[wazo]

thanks for the reply, have done that but it shows all fields and doesnt remove the ones that match

Jobs.id != applications.job_id

[php_daemon]

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

[wazo]

shouldnt there be a ! before the =?

[php_daemon]

Nope,

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

[wazo]

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 ''

[php_daemon]

Which means there are no such rows in jobs table that have no counterparts in applications table. Is that possible?

[wazo]

applications

job_id
4
5
4
3


Jobs

id

1
2
3
4
5
6
7
8
9
10
11

[php_daemon]

Can you run a simple query:

SELECT jobs.* 
FROM jobs
LEFT JOIN applications ON jobs.id = applications.job_id
WHERE applications.job_id IS NULL

[wazo]

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)

[php_daemon]

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:

SELECT jobs.* 
FROM jobs
LEFT JOIN applications ON jobs.id = applications.job_id
WHERE applications.job_id IS NULL

[wazo]

works fine, could it be anything else?

[php_daemon]

And if you add your AND sitesystem='0' ?

[siteguru]

SELECT jobs.*
, date_format(startdate,'%d/%m/%Y') AS startdate

??

[php_daemon]

^ AS is optional