在此表的左联接之后如何使用左外部联接? [英] how to use left outer join after left join for this tables?

问题描述

我要联接4个表以便在其中获取必要的数据.

I have 4 tables that I want to join in order to get the necessary data in them.

表A.

id | name | deleted | amount | due_date   | status
1  | a    | 0       | 10     | 2016-07-18 | Unpaid
2  | b    | 0       | 20     | 2016-07-19 | Unpaid
3  | c    | 0       | 15     | 2016-07-18 | Unpaid

表B

id | name   | due_date   | status
1  | a      |            | Unpaid
2  | b      |            | Unpaid
3  | c      |            | Unpaid
4  | d      | 2016-07-19 | Unpaid

表C

id | table_d_id | table_a_id
1  | 1          | 1
2  | 2          | 2
3  | 3          | 3

表D

id | 
1
2
3

我在这里想要做的是检索表B上具有到期日期和未付款状态的数据，并且在表A中找不到该数据.

What I want to do here is to retrieve the data at Table B with due_date and an unpaid status, and that it is not found in Table A.

我当前的查询

SELECT  A.*, B.*, C.*, D.*  
FROM A  
LEFT OUTER JOIN B ON B.name= A.name 
INNER JOIN C ON A.id = C.id  
INNER JOIN D ON C.id = D.id  
WHERE A.deleted=0 AND ((B.due_date < "2016-07-20" AND B.status = "Unpaid") OR (A.due_date < "2016-07-20" AND A.status = "Unpaid")) 
GROUP BY D.id

此查询当前所执行的操作是，它仅获取表B中的数据(表B的ID 4除外)，我也希望检索该数据，因为它具有未付款状态.

What this query currently does is that it only gets the data in the tables except for id 4 at table B, Which I was hoping to retrieve also since it has an unpaid status.

推荐答案

您应该使用左连接(并且在示例中，b.column是名称而不是数字..)

You should use left join (and in you sample the b.column is name not number ..)

SELECT  A.*, B.*, C.*, D.*  
FROM B  
LEFT JOIN A_cstm ON A.id = A_cstm.id_c  
LEFT  JOIN A ON B.name = A.name 
INNER JOIN C ON A.id = C.id  
INNER JOIN D ON C.id = D.id  
WHERE A.deleted=0 AND ((B.due_date < "2016-07-20" AND B.status = "Unpaid") OR (A.due_date < "2016-07-20" AND A.status = "Unpaid")) 
GROUP BY D.id

您的模型中未描述表A_cstm(为了清楚起见，我已将其删除..) 我已经更改了使用FROM B的连接顺序，并使用左连接A而不是您的FROM A ..左连接B

The table A_cstm is not described in you model (i have removed for clarity ..) and i have change the order of th join using FROM B and left join A instead of your FROM A .. left join B

SELECT  A.*, B.*, C.*, D.*  
FROM B  
LEFT  JOIN A ON B.name = A.name 
INNER JOIN C ON A.id = C.id  
INNER JOIN D ON C.id = D.id  
WHERE A.deleted=0 AND ((B.due_date < "2016-07-20" AND B.status = "Unpaid") OR (A.due_date < "2016-07-20" AND A.status = "Unpaid")) 
GROUP BY D.id    

这篇关于在此表的左联接之后如何使用左外部联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！