跨两个表构建一个mysql查询

Question

I have a table job_to_universities with the fields

id  univ_id  post_id
1     2        3
2     3        3
3     5        5
4     1        8
5     2        8

I have another table job_postings with the fields

id(post_id)  is_public
1             1
2             0
3             1
4             1
5             1 
6             0
7             0
8             0

say for an university id of 5 i want to get all the jobs from the job_postings table along with the jobs that have is_public as 1 in the job_postings table and i am stuck on this.

Answer 1

If I understand you correctly, you wish to get all job postings that are both public and related to an university. This would imply a fairly simple join:

SELECT ... FROM job_postings NATURAL JOIN job_to_universities
WHERE univ_id = ? AND is_public = 1

If you wish to get all job postings that are either public or related to an university, you can write something along the lines of:

SELECT ... FROM job_postings WHERE is_public = 1 
OR post_id IN (SELECT post_id FROM job_to_universities WHERE univ_id = ?)

Or use UNION DISTINCT:

SELECT ... FROM job_postings WHERE is_public = 1
UNION DISTINCT
SELECT ... FROM job_postings NATURAL JOIN job_to_universities
WHERE univ_id = ?