CODEWITHSUNDEEP
sqlsql-serverjoin
Shimrod
Shimrod

Reputation: 3205

Outer join on 3 tables

I have 3 tables, Client, Tool and ClientTools. One client can have multiple tools, so ClientTools acts as pivot table (only containing Id's).

What I would like to do is, for a given Client, to have a full list of tools, and a flag indicating whether the Client has this tool or not.

Where I came so far is :

select      t.Id as [ToolId],
            t.Name as [ToolName],
            Cast(case when c.Id is NULL then 0 else 1 end as bit) as [HasThisTool],
from        Tool t
Left join   ClientTools ct
on          t.Id = ct.ToolId
Left Join   Client c
on          ct.ClientId = c.Id

Which correctly gives me all Tools, but for all Clients (duplicating Tool rows when multiple clients own this Tool).

But as soon as I use a where close to filter to the selected client, my query only returns the rows for this client (so the left join is not made anymore).

I tried adding where c.Id = 123 and where (c.Id = 123 or c.Id is null) but none worked.

What am I missing ?

Thanks in advance !

Upvotes: 0

Views: 378

Answers (3)

Ben Thul
Ben Thul

Reputation: 32737

If you switch up the order of your tables, it should work:

select      t.Id as [ToolId],
            t.Name as [ToolName],
            Cast(case when t.Id is NULL then 0 else 1 end as bit) as [HasThisTool],
from        Client c
Left join   ClientTools ct
on          c.Id = ct.ClientId
Left Join   Tool t
on          ct.ToolId = t.Id
where       c.id = 123

In essence, you're saying that Client (not tool) is what determines whether a row shows up in the result set or not.

Upvotes: 0

Lamak
Lamak

Reputation: 70678

In your query, if you are not retrieving the name of your client, you don't need to join to that table (but its not the real problem). Try this:

select      t.Id as [ToolId],
            t.Name as [ToolName],
            Cast(case when ct.Id is NULL then 0 else 1 end as bit) as [HasThisTool]
from        Tool t
Left join   (SELECT * FROM ClientTools WHERE ClientId = @ClientId) ct
on          t.Id = ct.ToolId

Upvotes: 1

user359040
user359040

Reputation:

Try:

select      t.Id as [ToolId],
            t.Name as [ToolName],
            Cast(case when ct.Id is NULL then 0 else 1 end as bit) as [HasThisTool]
from        Tool t
Left join   ClientTools ct
on          t.Id = ct.ToolId and ct.ClientId = @ClientId

Upvotes: 1

Related Questions