时间戳

首页 > 解决方案 > 使用 MYSQL 查询加入 2 个带有 if 条件的表

mysql - 使用 MYSQL 查询加入 2 个带有 if 条件的表

问题描述

我有 2 个表：用户和 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')

标签： mysqlsql

解决方案

与CASE：

SELECT
  CONCAT(users.first_name, ' ', users.last_name) AS drafter_name,
  CASE prov_spec_search.proj_status
    WHEN 'Ongoing' THEN 'Not Available'
    ELSE 'Available'
  END proj_status   
FROM users LEFT JOIN prov_spec_search 
ON users.id = prov_spec_search.drafter_id
WHERE (users.activ_status ='1')

请参阅演示。
结果：

| drafter_name | proj_status   |
| ------------ | ------------- |
| Gokul menon  | Not Available |
| sudha dravid | Available     |
| sujith nair  | Available     |

推荐阅读