mysql - 加入或包含 null

Question

我有两个表

第一个叫做分支

| branch_id | location | phone | client_id(FK) | teamleader_id(FK)|
|      1    | Guadalupe| 11111 |       1       |         1        |

第二个叫客户端

| client_id | clientname | priority |
|      1    |    PNB     |  High    |

第三个叫employee

| Employee_id | firstname | lastname |
|      1      |   Rinnie  | Salvacion|

我试过查询结果是这样的..

 | branch_id | location | phone | client_id(FK) | teamleader_id(FK)| client_id | clientname | priority | Employee_id | firstname | lastname |
 |     1     | Guadalupe| 11111 |      1        |         1        |     1     |      PNB   |   High   |       1     |   Rinnie    Salvacion

这是我的代码

SELECT C.*, B.*, E.* 
FROM branch AS B
LEFT JOIN CLIENT AS C ON C.client_id = B.branch_ID
JOIN employee AS E ON E.employee_id = B.teamleader_ID 

Where (B.location = 'PNB' OR C.clientname = 'PNB') OR (B.location is null OR C.clientname = 'PNB')

我想显示两个结果，一个包含我的第一个查询中的所有信息，另一个显示所有表，但当您搜索“PNB”时只有客户端表有数据，其他表将为空。 .

请帮帮我..

Answer 1

考虑联合查询，合并两个结果集:

    SELECT b.branch_id, b.location, b.phone, b.teamleader_id, 
           c.client_id, c.clientname, c.priority, 
           e.Employee_id, e.firstname, e.lastname
      FROM branch as b
 LEFT JOIN client AS c ON c.client_id = b.client_id
INNER JOIN employee AS e ON e.employee_id = b.teamleader_ID
     WHERE (b.location = 'PNB' OR c.clientname = 'PNB') 
        OR (b.location is null OR c.clientname = 'PNB');

     UNION

    SELECT NULL, '', '', NULL, 
           c.client_id, c.clientname, c.priority, 
           NULL, '', ''
      FROM client AS c
     WHERE c.clientname = 'PNB';