Help with Joins

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.

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

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)