使用MYSQL查询将2个表与if条件联接在一起

三重梅农

我有2个表：users和prov_spec_search。我正在寻找一个符合以下条件的SQL查询来联接这些表：

users Table:

id | first_name | last_name | activ_status
-------------------------------------------
6  | sudha      | dravid    |    1
7  | Gokul      | menon     |    1
13 | sujith     | nair      |    1
-------------------------------------------

prov_spec_search表

id | inv_user_id | drafter_id | proj_status
-------------------------------------------
1  | 13          | 7          |  Ongoing
2  | 13          | 6          |    

-------------------------------------------

预期结果是：

Need to join users and prov_spec_search tables with 2 conditions where 

users.id = prov_spec_search.drafter_id

      AND 

if project_status = Ongoing then the result proj_status should show 'Not Available'. 
if proj_status is null then should show 'Availabe'. 
if no drafters_id or no records found then from users table it should show the users name with option(proj_status) 'Available'

结果：

Name         |   proj_status   
------------------------------
Gokul Menon  |   Not Available  
sudha dravid |   Available 
sujith Nair  |   Available

我从以下查询开始：

SELECT
CONCAT(users.first_name, ' ', users.last_name) AS drafter_name,
users.id AS id,
users.activ_status,
prov_spec_search.proj_current_status
FROM users
LEFT JOIN prov_spec_search ON users.id = prov_spec_search.drafter_id
WHERE (users.activ_status ='1')

亚历克斯·巴克

SELECT 
  users.id AS id,
  CONCAT(
    users.first_name, 
    ' ', 
    users.last_name
  ) AS drafter_name,
  users.activ_status,
  IF (
    prov_spec_search.proj_current_status = 'Ongoing', 
    'Not Available', 
    COALESCE(prov_spec_search.proj_current_status, 'Available')
  ) AS proj_status
FROM users

LEFT JOIN prov_spec_search 
ON users.id = prov_spec_search.drafter_id

WHERE users.activ_status = 1

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。