-3

I have two tables all_jobs and jobs_applied. Problem is I want to get the list of jobs from all_jobs table. At the moment I have this query but it is only giving me common records from both table.

SELECT b.uid 
     , a.jobtitle
     , a.job_id

  FROM job_posting a 
  left 
  join job_applied b 
    on a.job_id = b.job_id
Where b.uid != 10 and disable=0 and draft =0

Edit

in job_posting table there are 24 records. in job_applied table there are 2 records. Both table have job_id field. When I execute the query I am expecting 23 records because one record is common in both table. But I am getting single record.

I have checked this question. What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? but not sure how can I use it in my situation.

Edit 2 (table structure)

job_posting

job_id,
title,
skills,
post_date

job_applied

id,
job_id,
uid,
apply_date

What I am trying to achieve: I want to get the list of records from job_posting table but those records shouldn't be exist in job_applied table with specific uid. So, it means i will have to add two things in where clause one for uid and another one for job_id.

I also tried below query but it is returning 0 records.

SELECT 
          a.jobtitle
         , a.job_id

      FROM job_posting a 
      left 
      join job_applied b 
        on a.job_id = b.job_id
Where (b.job_id is null and a.disable=0 and a.draft =0 and b.uid !=10)

Edit 3

I am using this query now and it is returning correct result.

Select a.jobtitle, a.job_id from job_posting a where 
a.job_id not in (select b.job_id from job_applied b WHERE b.uid=1) 
AND a.disable=0 AND a.draft =0

Let me know if I am doing anything wrong.

halfer
  • 19,824
  • 17
  • 99
  • 186
Roxx
  • 3,738
  • 20
  • 92
  • 155
  • I am not sure what is the problem. Could you show results example and expected results? – Jakub Szumiato May 15 '16 at 18:17
  • How does table job_posting relates to all_jobs? – Jakub Szumiato May 15 '16 at 18:19
  • I have update the question. – Roxx May 15 '16 at 18:23
  • This query would result in a syntax error. – Strawberry May 15 '16 at 18:27
  • @Strawberry that is typo in this question. I hope you understand. – Roxx May 15 '16 at 18:29
  • 1
    It's hard to understand what do you really need. I understand your table schema, but I don't know what is your real need. What brings the confusion here is this `b.uid != 10`. You have to know, that left join will fill all fields of not matched rows with null. So if you select rows that b.job_id is null and b.uid !=10 ,that does not make any sense, b.uid will be null in such case and the result of such comparison the same. Maybe you want to select rows that (don't have matching rows in table b) OR (have matching row, but b.uid != 10)? – Jakub Szumiato May 16 '16 at 06:16
  • @JakubSzumiato It is very simple. Let me explain. When user apply for job a record added in job_applied table. I want to show the jobs to user those he can apply from job_posting table. i don't want to show jobs those he applied earlier. Now you can see the schema of table. i hope it makes sense to you. – Roxx May 16 '16 at 07:09
  • 1
    So once he applies to a job, a new record is inserted in to job_applied and you don't want to show this job to him. That's easy, you're right. You should simply remove the b.uid != 10 from your query and leave this b.job_id is null. That should do the trick I guess. – Jakub Szumiato May 16 '16 at 07:11
  • Please check my updated answer with sqlfiddle link. – Jakub Szumiato May 16 '16 at 07:29
  • @JakubSzumiato One question comes in my mind. if there are multiple users applied for the job then does it will provide me the correct output because we are not adding uid in sql query. So, how query will got to know which user he has to ignore. – Roxx May 16 '16 at 08:14
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/112056/discussion-between-jakub-szumiato-and-calculating-machine). – Jakub Szumiato May 16 '16 at 12:12
  • I just noticed your last comment. I think you're doing everything properly, which may be obvious if you're getting right results. If you still have questions to answer, let us know. – Jakub Szumiato May 17 '16 at 15:28

5 Answers5

4

After all your comments, I understood that b.uid is a reference to some users table. So the job_applied is a kind of association table for many-to-many relation. It seems your query should look like that:

SELECT a.jobtitle, a.job_id
FROM job_posting a 
WHERE a.disable=0 AND a.draft =0
AND job_id NOT IN (
SELECT job_id FROM job_applied b
WHERE b.uid = 1)

This will remove matched rows from table b for user with uid = 1. This should be provided via some parameter in a procedure or something.

I have prepared a fiddle for you to test: http://sqlfiddle.com/#!9/91792/7/0 Let me know if that helps.

Jakub Szumiato
  • 1,318
  • 1
  • 14
  • 19
  • I tried that but it didn't return any record. voted up – Roxx May 15 '16 at 18:36
  • It shouldn't normally happen as in left join unmatched rows are filled with nulls. I think that the condition b.uid should be removed as well as b.uid will be null – Jakub Szumiato May 15 '16 at 18:43
  • Could you add alias to all columns checked in where clause? (Eg disable ) – Jakub Szumiato May 15 '16 at 18:44
  • I make little bit changes in table schema and now it is quite similar to my table. – Roxx May 16 '16 at 08:19
  • When i execute the query i am not getting the correct data. It is removing the data from all uid. I am expecting it should remove the data for specific user. – Roxx May 16 '16 at 08:26
1

Have you tried a left outer join? I think the join you are using will only display the common results.

Strainger
  • 134
  • 1
  • 5
1

in job_posting table there are 24 records. in job_applied table there are 2 records. Both table have job_id field. When i execute the query i am expecting 23 records because one record is common in both table

I think a left join on job_posting should return all the records on that table (matching the conditions), despite a record on the another table is related with two or more. You probably are seeing 23 records on your results because of the conditions, check it:

Where b.uid != 10 and disable=0 and draft =0
javier_domenech
  • 5,995
  • 6
  • 37
  • 59
0

When you want to get data from this query with loop you should try to like this :

`SELECT a.jobtitle as jTitle, a.job_id as jId
FROM job_posting a 
WHERE a.disable=0 AND a.draft =0
AND job_id NOT IN (
SELECT job_id FROM job_applied b
WHERE b.uid = 1)`

like foreach <?=$query['jTitle']?>

Polas Habib
  • 9
  • 2
  • 8
0

You may get the same result with better performance in this way.

SELECT a.jobtitle, a.job_id
    FROM job_posting a
    LEFT JOIN job_applied b ON a.job_id=b.job_id
WHERE b.job_id IS NULL and a.disable=0 and a.draft =0 and b.uid=1;
Ajith
  • 639
  • 6
  • 21