CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Álvaro García
Álvaro García

Reputation: 19356

Is it possible to filter in a left join?

I have three tables, Clients, Bills and BillsStates. I would like to get always the client and if it has bills, only the bills that can be modified. I am trying something like that:

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

The problem with that is that I get all the bills of the client, no matter if they can be modified or not.

I have tried to with a right join, but in this case I have not get any result.

Is it possible with joins or perhaps I need some subquery? I would prefer a solution with joins, but if there is no way to do it in this way, I would accept another solution.

Upvotes: 0

Views: 74

Answers (5)

Caleth
Caleth

Reputation: 62576

Rather than joining on a subquery, you can also re-arrange the order of joins

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

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272006

Your query simply replaces BillsStates.* with null values where BillsStates.AllowModify = 1 condition fails:

| 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        |

Rearrange the join type and condition to get the desired result:

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        |

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use inner join between bills and BillsStates instead left join and do left join with client

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

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

you can try this.

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

Upvotes: 1

user743382
user743382

Reputation: 

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

The problem you have is that you only cause the BillsStates record to be excluded, because your filter is only in its join condition. Instead, you can re-order and move it into Bills's join condition.

Upvotes: 3

Related Questions