代码之家 › 专栏 › 技术社区 › Álvaro García

是否可以在左连接中进行筛选?

join tsql sql-server sql

Álvaro García · 技术社区 · 8 年前

我有三张桌子,客户,账单和账单。我想得到的总是客户,如果它有账单,只有账单可以修改。我试着这样做:

select * from Clients
left join Bills on Bills.IDClient = Clients.IDClient
left join BillsStates on BillsStates.IDBillState = Bills.IDState
and BillsStates.AllowModify = 1

问题是我得到了客户的所有账单,不管它们是否可以修改。

我试过用右连接,但在这种情况下,我没有得到任何结果。

5 回复 | 直到 8 年前

Salman Arshad 8 年前

您的查询只是替换 BillsStates.* 具有空值,其中 BillsStates.AllowModify = 1 条件失败:

| IDClient | Name | IDClient | IDState | Name   | IDBillState | AllowModify |
|----------|------|----------|---------|--------|-------------|-------------|
| 1        | John | 1        | 1       | Bill 1 | NULL        | NULL        |
| 1        | John | 1        | 2       | Bill 2 | 2           | 1           |
| 2        | Jane | NULL     | NULL    | NULL   | NULL        | NULL        |

重新排列联接类型和条件以获得所需的结果:

SELECT *
FROM Clients
LEFT JOIN (Bills
INNER JOIN BillsStates ON BillsStates.IDBillState = Bills.IDState) ON Bills.IDClient = Clients.IDClient AND BillsStates.AllowModify = 1;

| IDClient | Name | IDClient | IDState | Name   | IDBillState | AllowModify |
|----------|------|----------|---------|--------|-------------|-------------|
| 1        | John | 1        | 2       | Bill 2 | 2           | 1           |
| 2        | Jane | NULL     | NULL    | NULL   | NULL        | NULL        |

user743382 user743382 8 年前

select * from Clients
left join Bills
    inner join BillsStates on BillsStates.IDBillState = Bills.IDState
on Bills.IDClient = Clients.IDClient
and BillsStates.AllowModify = 1

BillsStates Bills

Serkan Arslan 8 年前

你可以试试这个。

select * from Clients
    left join 
        ( select * from Bills 
            inner join BillsStates on BillsStates.IDBillState = Bills.IDState
                and BillsStates.AllowModify = 1
        ) B ON  B.IDClient = Clients.IDClient

Zaynul Abadin Tuhin 8 年前

在bills和BillsStates之间使用内部连接,而不是左连接,并与客户机进行左连接

select c.* from Clients c
left join Bills b on b.IDClient = c.IDClient
inner join BillsStates bs 
on bs.IDBillState = b.IDState  and   b.AllowModify = 1

Caleth 8 年前

也可以重新排列联接顺序,而不是对子查询进行联接

SELECT c.*, b.*, bs.* -- Todo: only the relevant columns
FROM Bills b
JOIN BillsStates bs ON BillsStates.IDBillState = Bills.IDState
RIGHT JOIN Clients c ON b.IDClient = c.IDClient