如何在mySql中获取用户未申请的所有作业 [英] How to get all jobs that user has not applied to in mySql

问题描述

我必须填写表格，job_postings 和 job_applies.我如何获得该用户尚未申请的所有职位?

I have to tables, job_postings and job_applies. How can I get all the jobs, that user has not applied to?

以下是我的job_postings 表的列:

id, user_id, title, description, duties, salary, child_count, benefits, created_at, updated_at

下面是job_applies表的列

user_id, posting_id, status, created_at, updated_at

我尝试了什么:

  $job_postings =  DB::table('job_postings')
        ->select(
          'job_postings.title',
          'job_postings.description',
          'job_postings.duties',
          'job_postings.salary',
          'job_postings.child_count',
          'job_postings.benefits',
          'job_postings.created_at',
          'job_postings.id AS posting_id',
          'job_postings.user_id')
        ->join('job_applies', 'job_applies.posting_id', '!=', 'job_postings.id')
        ->where('job_applies.user_id', "=" , user()->id)
        ->get();

推荐答案

在 Mysql 中你应该写(用你的字段列表替换 *).你可以适应你的语言

In Mysql you should write (replace * with your field list). You can adapt to your language

 SELECT * 
 FROM JOB_POSTING A
 LEFT JOIN JOB_APPLIES B ON B.POSTING_ID = A.ID AND B.USER_ID = A.USER_ID
 WHERE B.POSTING_ID IS NULL

或

 SELECT * 
 FROM JOB_POSTING A
 WHERE NOT EXISTS (SELECT 1 FROM JOB_APPLIES B WHERE B.POSTING_ID = A.ID  AND B.USER_ID = A.USER_ID)

这篇关于如何在mySql中获取用户未申请的所有作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！