php - 加入具有 2 个条件的 2 个表(MySQL 和 PHP)

Question

我有 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 | activ_status
-------------------------------------------
1  | 13          | 7          |    1
2  | 13          | 6          |    1
-------------------------------------------

预期结果是:

Need to join users and prov_spec_search tables with 2 conditions where 

prov_spec_search.inv_user_id = users.id = Name

      AND 

prov_spec_search.drafter_id = users.id = Drafter name

结果:

Name       |   Drafter Name   | Active
------------------------------------------
sujit nair |   Gokul menon    |  active
sujit nair |   sudha dravid   |  active

我从这个查询开始:

SELECT
prov_spec_search.id,
prov_spec_search.inv_user_id,
prov_spec_search.drafter_id,
prov_spec_search.activ_status,
users.first_name,
users.last_name,
users.activ_status AS activ_status1
FROM prov_spec_search
INNER JOIN users ON prov_spec_search.inv_user_id = users.id AND prov_spec_search.drafter_id = users.id
WHERE (prov_spec_search.activ_status ='1')

Answer 1

您需要从 prov_spec_search 表连接到 users 表，两次:

SELECT
    pss.id,
    pss.inv_user_id,
    pss.drafter_id,
    pss.activ_status,
    CONCAT(u1.first_name, ' ', u1.last_name) AS Name,
    CONCAT(u2.first_name, ' ', u2.last_name) AS Drafter_Name
FROM prov_spec_search pss
LEFT JOIN users u1
    ON pss.inv_user_id = u1.id
LEFT JOIN users u2
    ON pss.drafter_id = u2.id
WHERE
    pss.activ_status = 1;
    -- you may filter user and drafter names here if desired

Demo