SQL Server Performance

Help with Joins

Discussion in 'T-SQL Performance Tuning for Developers' started by zee, Apr 21, 2004.

  1. zee New Member

    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
  2. ChrisFretwell New Member

    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)

Share This Page