如何使用MySQL查询列出CI数据，并仅列出未过期的已张贴雇主和职务

Question

表users

id     username           password
----------------------------------
1      email@yahoo.com    admin
2      email1@yahoo.com   password
3      example@m.com      admin123

表employer-profile

id     u_id   company-name       logo
-----------------------------------------
1      2      Blue Rays Graphics logo.jpg
2      1      Hire Me to do best logo.jpg
3      3      example.com        logo.jpg

表employer-post-job

id     e_id   job-title     job-category    expire date
-------------------------------------------------------
1      2      web-developer IT              2013-9-5
2      1      receptionist  media           2013-9-5
3      2      web-designer  IT              2013-9-5

在本表中，我希望像下面这样在索引页中列出数据

------------
|          |  Blue Rays Graphics
|   logo   |
|          |
------------

------------
|          |  Hire Me to do best
|   logo   |
|          |
------------

在本表中，我希望列出雇主哪个职务未过期，如果过期，雇主发布的所有职务都比只隐藏雇主列表更好，我希望通过mysql查询在CI框架中列出它。

我试过这个

SELECT *
FROM employer_profile
WHERE u_id IN
        ( SELECT DISTINCT u_id
         FROM employer_profile
         INNER JOIN
             ( SELECT e_id,
                      COUNT(*)
              FROM employer_post_job
              WHERE dead_line >= CURDATE()
              GROUP BY e_id )
         INNER JOIN users ON employer_profile.u_id = users.id);

Answer 1

尝试这个查询，如果您仍然面临问题，因为我无法执行它。

Select * from users u 
          inner join employer-profile e ON u.id = e.u_id
          inner join employer-post-job epj ON e.u_id =epj.e_id
          where epj.expire date > '".date('Y-m-d')."'