Mysql选择没有在网站上发帖的用户

Question

我想选择没有发布过工作的用户。

我已经试过了:

SELECT 
    e.id, e.companyName, ee.emailAddress, ee.firstName, ee.surname    
FROM 
    employer as e, country as country, employerUser as ee , countryRegion as re, job as j 
WHERE 
    country.iso2=e.countryISO2FK AND ee.employerIDFK=e.id AND ee.originalUser=1 AND country.iso2='GB' AND (e.regionCodeFK ='GTL' AND e.regionCodeFK = re.code OR e.regionCodeFK ='' OR e.city ='' ) AND e.status=1 AND e.isActive=1 AND e.id=j.employerIDFK AND j.employerIDFK IS NULL

有人知道我做错了什么吗?

Answer 1

您正在使用带有WHERE过滤器的CROSS JOIN，该过滤器不会让您找到不存在的项。您想要在'job'表上使用LEFT JOIN，如下所示:

SELECT e.id, e.companyName, ee.emailAddress, ee.firstName, ee.surname 
FROM employer as e
  JOIN country ON country.iso2=e.countryISO2FK
  JOIN employerUser as ee ON ee.employerIDFK=e.id
  LEFT JOIN job as j ON e.id=j.employerIDFK 
WHERE ee.originalUser=1 
  AND country.iso2='GB' 
  AND (e.regionCodeFK ='GTL' AND e.regionCodeFK = re.code OR e.regionCodeFK ='' OR e.city ='' ) 
  AND e.status=1 AND e.isActive=1  
  AND j.employerIDFK IS NULL