Select from two tables and remove where ID matches

Question

I have two tables: job_postings and job_apply. If the user applies to a job, it will inserted into the job_apply table. Now after page reload, I would like to show the user all the jobs except the one he just applied to. How can I accomplish this?

this is the job_apply table: job_id, user_id, last_apply_date

this is the job_postings table job_id, user_id, job_title, job_description, job_wage, job_child_count, job_date, job_time_from, job_time_to, add_inserted

I tried this:

SELECT tbl_users.user_id AS poster_id, userFirstName, userLastName, job_apply.job_id, job_postings.user_id, job_apply.user_id, job_title, job_description, job_wage, job_child_count, job_date, job_time_from, job_time_to, add_inserted, 
DATE_FORMAT(add_inserted,'%d %b %Y, at %T') AS add_inserted 
FROM job_postings 
JOIN tbl_users ON job_postings.user_id = tbl_users.user_id 
JOIN job_apply ON job_postings.job_id = job_apply.job_id
WHERE job_apply.job_id = job_postings.job_id
AND job_apply.user_id = 94
ORDER BY add_inserted DESC;

EDIT

update query

    SELECT tbl_users.user_id AS poster_id, userFirstName, userLastName, job_postings.job_id, job_postings.user_id, job_apply.user_id, job_title, job_description, job_wage, job_child_count, job_date, job_time_from, job_time_to, add_inserted,
  DATE_FORMAT(add_inserted,'%d %b %Y, at %T') AS add_inserted
  FROM job_postings
  JOIN tbl_users ON job_postings.user_id = tbl_users.user_id
  LEFT JOIN job_apply ON job_postings.job_id = job_apply.job_id
  AND job_apply.user_id = 83
  GROUP BY job_postings.job_id
  ORDER BY add_inserted DESC

EDIT 3

So i used this:

SELECT tbl_users.user_id AS poster_id, userFirstName, userLastName, job_postings.job_id, job_postings.user_id, job_apply.user_id, job_title, job_description, job_wage, job_child_count, job_date, job_time_from, job_time_to, add_inserted,
  DATE_FORMAT(add_inserted,'%d %b %Y, kell %T') AS add_inserted
  FROM job_postings
    JOIN tbl_users ON job_postings.user_id = tbl_users.user_id

       JOIN (SELECT * FROM job_apply WHERE job_id IS NOT NULL) job_apply ON job_postings.job_id = job_apply.job_id

WHERE  job_apply.user_id = 94

This shows me the rows where user has applied, but I would like to get the values where user has NOT applied to. Any suggestions?

please show the updated query. Also note that if you use a join you need to remove the `WHERE job_apply.job_id = job_postings.job_id` that is redundant to the join — Lelio Faieta, Sep 20 '16 at 09:36
@LelioFaieta sql fiddle is not my friend. Cannot insert data. — z0mbieKale, Sep 20 '16 at 09:47

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

add AND job_apply.job_id IS NULL try this..

SELECT tbl_users.user_id AS poster_id, userFirstName, userLastName, job_apply.job_id, job_postings.user_id, job_apply.user_id, job_title, job_description, job_wage, job_child_count, job_date, job_time_from, job_time_to, add_inserted, 
DATE_FORMAT(add_inserted,'%d %b %Y, at %T') AS add_inserted 
FROM job_postings 
JOIN tbl_users ON job_postings.user_id = tbl_users.user_id 
LEFT JOIN job_apply ON job_postings.job_id = job_apply.job_id
WHERE job_apply.job_id = job_postings.job_id
AND job_apply.user_id = 94 AND job_apply.job_id IS NULL
ORDER BY add_inserted DESC;

and you can get more details about join's HERE..

edited May 23 '17 at 11:51

Community

1
1

answered Sep 20 '16 at 09:41

Sarath

2,318
1
12
24

getting 0 rows. – z0mbieKale Sep 20 '16 at 09:47
Im on it.. keeps messing with me. give me few minutes – z0mbieKale Sep 20 '16 at 09:49
yeah this is not working. the fiddle keeps throwing me errors. I can build the schema but cannot insert any data or select. – z0mbieKale Sep 20 '16 at 09:52

score 0 · Accepted Answer · answered Sep 20 '16 at 12:19

Here is the final solution

SELECT tbl_users.user_id AS poster_id, userFirstName, userLastName, job_postings.job_id, job_postings.user_id, job_title, job_description, job_wage, job_child_count, job_date, job_time_from, job_time_to, add_inserted,
  DATE_FORMAT(add_inserted,'%d %b %Y, at %T') AS add_inserted
  FROM job_postings
  JOIN tbl_users ON job_postings.user_id = tbl_users.user_id

WHERE NOT EXISTS (
SELECT job_id FROM job_apply 
WHERE job_postings.job_id = job_apply.job_id AND job_apply.user_id = 116    
)

Select from two tables and remove where ID matches

EDIT

EDIT 3

2 Answers2