Nested Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nested Joins

Author

Topic

zeeMan
Starting Member

1 Post

Posted - 2004-04-21 : 15:00:11

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources