nav.sql

/*sports*/
select top 200 t.Profile_ID, t.Name, t.Parent_id, case when exists (select p.avis from web.dbo.profile p (nolock) where p.Avis = t.Avis and p.Parent_id = t.Profile_id) then 1 else 0 end as Children, case when IsNull(relp.url , '') <> '' then relp.url else IsNull(t.URL, '')   end as URL,  case    when IsNull(relp.TargetURL , '') <> '' then relp.TargetURL else IsNull(t.TargetURL, '') end as TargetURL, case when ISNULL(relp.TargetCategory, '') <> '' then relp.TargetCategory else IsNull(t.TargetCategory, '') end as TargetCategory, web.dbo.fGetProfileLevel('GA','1875') as Sublevel from web.dbo.Profile t (nolock)   left join  webextras.dbo.profile_extrafields ext (nolock) on (ext.Avis=t.Avis and ext.ProfileID=t.profile_id and ext.Varname='ProfileIDRef')  left join web.dbo.profile relp (nolock)    on (relp.Avis=t.Avis and relp.profile_id=cast(ext.VarValue as int)) where t.Avis = 'GA' and t.Profile_ID > 0 and t.Parent_ID = '1875'   and t.Profile_ID NOT IN (SELECT [ProfileID]                            FROM [webextras].[dbo].[Profile_ExtraFields] (nolock)                            WHERE Avis = 'GA'   and Varname = 'visibility' and (substring(VarValue, 1, 2) = 'V99999' OR substring(VarValue, 1, 2) = 'V0'))   and t.Active = 1 order by t.sortcode, t.Name 



/*prep sports*/
select top 200 t.Profile_ID, t.Name, t.Parent_id, case when exists (select p.avis from web..profile p (nolock) where p.Avis = t.Avis and p.Parent_id = t.Profile_id) then 1 else 0 end as Children, URL = IsNull(t.URL, ''), TargetURL = IsNull(t.TargetURL, ''), TargetCategory = IsNull(t.TargetCategory, ''), web.dbo.fGetProfileLevel('GA','1875') as Sublevel from web..Profile t (nolock) where t.Avis = 'GA' and t.Profile_ID > 0 and t.Parent_ID = '1875' and t.Profile_ID NOT IN (SELECT [ProfileID] FROM [webextras].[dbo].[Profile_ExtraFields] (nolock) WHERE Avis = 'GA' and Varname = 'visibility' and (substring(VarValue, 1, 2) = 'V99999' OR substring(VarValue, 1, 2) = 'V0')) and t.Active = 1 order by sortcode, Name