SQL Server Performance Forum – Threads Archive

Help with Joins

Hi all, I have 4 tables, 2 have the available data to the user (one is a lookup table and the other has the data) the same applies to other two tables, and the other 2 tables have previously selected rows (whatever the user chose to select and save). For each row in the available row, a flag needs to be set to 1 if it’s found in the selected rows. How can I nest the last join so always all rows from available tables will show, and the flag will be set if a row is found in the selected table. The SP works if I keep the last join as left outer join, but when I use an inner join, it does not return anything if the cretria does not match. I still want to show all avialable data. CREATE PROCEDURE [dbo].[Mystuff_Sel]
(
@UserName varchar(25),
@ClientId int
)
AS Select Lookuptable.BrandId,
Lookuptable.BrandDesc,
Case When ChildSelectedTable.ChildSelectedTableId is null or ChildSelectedTable.BrandId <> Lookuptable.BrandId
Then 0 Else ChildSelectedTable.ChildSelectedTableId End as ChildSelectedTableId,
Cast(Case When ChildSelectedTable.ChildSelectedTableId is null or ChildSelectedTable.BrandId <> Lookuptable.BrandId
Then 0 Else 1 End as bit) as SolicitFlag,
Case When ChildSelectedTable.ChildSelectedTableId is null or ChildSelectedTable.BrandId <> Lookuptable.BrandId
Then 0 Else ChildSelectedTable.ClientSelectedParentId End as ClientSelectedParentId,
Case When ChildSelectedTable.ChildSelectedTableId is null or ChildSelectedTable.BrandId <> Lookuptable.BrandId
Then ‘0’ Else ChildSelectedTable.CustomerTypeCode End as CustomerTypeCode,
Case When ChildSelectedTable.ChildSelectedTableId is null or ChildSelectedTable.BrandId <> Lookuptable.BrandId
Then 0 Else ChildSelectedTable.BrandId End as BrandId,
Case When ChildSelectedTable.ChildSelectedTableId is null or ChildSelectedTable.BrandId <> Lookuptable.BrandId
Then 0 Else ChildSelectedTable.SolicitByProductLine End as SolicitByProductLine From Lookuptable with (nolock) Inner Join ClientAvailableTable with(nolock) on (ClientAvailableTable.ClientCustomerId = @ClientId And ClientAvailableTable.BrandId = Lookuptable.BrandId And ClientAvailableTable.ActiveFlag = 1) left outer join ClientSelectedParent with(nolock) on (ClientSelectedParent.ClientCustomerId = @ClientId And ClientSelectedParent.ActiveFlag = 1) Left outer Join ChildSelectedTable with (nolock) On (ClientSelectedParent.ClientSelectedParentId = ChildSelectedTable.ClientSelectedParentId
And ChildSelectedTable.activeFlag = 1)
Where (Lookuptable.ActiveFlag = 1) If @@Error <> 0
Return -1 Return 0
GO MS SQL 2000
Windows 2000 SP4

---

You cant change it to an inner join if you want any records that dont have a childselectedtable, so that join has to stay like that. You say it works with the left join…why do you need to put it in a full join? Is the full join returning data you dont want to have returned? You can change it all, I think to be a bit easier, but please try this to see if its what you want…
Select Lookuptable.BrandId,
Lookuptable.BrandDesc,
isnull(childselectedtableid,0) as ChildSelectedTableId,
Cast(Case When ChildSelectedTable.ChildSelectedTableId is null
Then 0 Else 1 End as bit) as SolicitFlag,
Case When ChildSelectedTable.ChildSelectedTableId is null
Then 0 Else ChildSelectedTable.ClientSelectedParentId End as ClientSelectedParentId,
Case When ChildSelectedTable.ChildSelectedTableId is null
Then ‘0’ Else ChildSelectedTable.CustomerTypeCode End as CustomerTypeCode,
Case When ChildSelectedTable.ChildSelectedTableId is null
Then 0 Else ChildSelectedTable.BrandId End as BrandId,
Case When ChildSelectedTable.ChildSelectedTableId is null
Then 0 Else ChildSelectedTable.SolicitByProductLine End as SolicitByProductLine
From Lookuptable with (nolock)
Inner Join ClientAvailableTable with(nolock) on (ClientAvailableTable.ClientCustomerId = @ClientId And ClientAvailableTable.BrandId = Lookuptable.BrandId And ClientAvailableTable.ActiveFlag = 1)
left outer join ClientSelectedParent with(nolock) on (ClientSelectedParent.ClientCustomerId = @ClientId And ClientSelectedParent.ActiveFlag = 1)
Left outer Join ChildSelectedTable with (nolock) On (ClientSelectedParent.ClientSelectedParentId = ChildSelectedTable.ClientSelectedParentId
And ChildSelectedTable.activeFlag = 1 and ChildSelectedTable.BrandId = Lookuptable.BrandI)
Where (Lookuptable.ActiveFlag = 1)

---

]]>