MYSQL query help

lippy88

Hi,
I am developing an online job portal app.When a user logs in, I want them to see a list of all jobs that they have not applied for. The sql query I am using currently is :

public void jobsQuery() throws SQLException {

String sql ="SELECT * FROM job LEFT JOIN candidatejob on job.IDjob = candidatejob.IDjob where candidatejob.username != '"+username+"'";

+username+ been the candidate in the session that is logged in!
The 3 tables concerned are job,candidate and candidateJob.
The query im using only returns the jobs within the candidateJob table that the username has not applied for.
If there is another job created in the Job table but has not yet any candidates allocated to this job(hence not appearing in candidateJob table)it should also appear in the list im trying to return,but I cant figure out how to!!

Help would be much appreciated,Thanks!!

starlit

Think 'left' could be 'inner or outer join'??

Have a look at this website might help with MYSQL resources.

http://dev.mysql.com/doc/index.html

On the left hand side you see a couple of different manual editions and you can view the manual for the edition you are using.

lippy88

Yes ive tried the INNER join already but that seems to give me the same result:( cant find anything useful in them references either,Thanks for the help though!!

starlit

No problem. Have you any SQL book on hand you could look at or research online?

amen

A bit off topic but you shouldn't be using the username like this.

What happens if there is spelling mistake in the username and its corrected? Are you going to update all the rows in candidatejob table?

you should really have User table with a userid and username. you then use the userid in candidatejob table. much nicer.

Don't be using Select *. Only select the columns you need.

If there is another job created in the Job table but has not yet any candidates allocated to this job

I'm been pedantic but the above requirement is different from

I want them to see a list of all jobs that they have not applied for

The first quote means that any job that has no candidate associated will appear in the list when a user logs on but the second means that any job that does not have the specific user will appear when the user logs on.

The SQL will be different for both. I am assuming its quote 2?

At the moment your query says give me all the jobs including those that do not have a name matching the candidate.username.

What you really want is all the jobs and and those that are associated with the candidate.username and where the job is not assigned to the username.

The SQL below is for SQL Server but should work for MySQL. You should format as required.

[PHP]
SELECT
*
FROM
#Job
LEFT OUTER JOIN
#CandidateJob
ON #JOB.JOBID= #CandidateJob.JOBID
AND #CandidateJob.UserName = username
WHERE #CandidateJob.JobID IS NULL
[/PHP]

Freddio

select job.ID, job.NAME from job where job.ID not in (select candidateJob.jobID from candidateJob where candidateJob.userID = candidate.ID and candidate.Name = '"+username+"'")


is what you need to debug

arleitiss

WHERE is always written in capitals.
(at least in my experience)

amen

WHERE is always written in capitals.
(at least in my experience)

what ? You do realise that SQL Commands are case insensitive i.e.
where, WHERE, WhErE etc will be correctly processed and return the correct results.

Normally SQL Commands are written in capitals to make the keywords stand out and to improve readability.

starlit

Yes 'WHERE' is always in caps.

Freddio

I suppose the fact that the op is trying != as opposed to <> is not as important as how pretty it looks

lippy88

amen wrote: »

The SQL below is for SQL Server but should work for MySQL. You should format as required.

[PHP]
SELECT
*
FROM
#Job
LEFT OUTER JOIN
#CandidateJob
ON #JOB.JOBID= #CandidateJob.JOBID
AND #CandidateJob.UserName = username
WHERE #CandidateJob.JobID IS NULL
[/PHP]

Amen, Thank you for your help this query worked perfect for me,just what I was looking for.
"SELECT * FROM job LEFT OUTER JOIN candidatejob ON job.IDjob = candidatejob.IDjob AND candidateJob.username = '"+username+"' WHERE candidatejob.IDjob IS null";