Disecting SQL Code

• yrstruly

  Hall of Fame

  Points: 3477

  More actions

  January 13, 2022 at 5:06 pm

  #3972817

  Please assist. I have this query:

  App Filter Categories

  select a.label as 'App Category'

  , b.label as 'Menu'

  , c.product_uid as 'Product UID'

  from app_menu_filters a

  left join app_menu_filters b on b.parent_uid=a.uid

  left join app_menu_filter_products c on b.uid=c.app_menu_filter_uid

  where a.status='A' and a.level=1

  order by 1,2,3;

  I cant seem to find where it draws the items e.g "App Category" have products such as 'Baby Products' and "Menu" products like 'BabyCare'. Here are the DDL's for the tables:

  USE [BottlesMarketing]

  GO

  /****** Object: Table [dbo].[App_menu_filter_products] Script Date: 2022/01/13 18:59:40 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[App_menu_filter_products](

  [uid] [int] NULL,

  [product_uid] [int] NULL,

  [app_menu_filter_uid] [int] NULL

  ) ON [PRIMARY]

  GO

   

  AND

   

  USE [BottlesMarketing]

  GO

  /****** Object: Table [dbo].[App_menu_filters] Script Date: 2022/01/13 19:03:37 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[App_menu_filters](

  [uid] [bigint] NULL,

  [tenant_uid] [int] NULL,

  [label] [nvarchar](50) NULL,

  [sub_copy] [nvarchar](300) NULL,

  [opt_order] [int] NULL,

  [level] [int] NULL,

  [icon] [nvarchar](100) NULL,

  [header] [nvarchar](50) NULL,

  [parent_uid] [int] NULL,

  [minimum_stock_balance] [numeric](10, 2) NULL,

  [atp_percent] [numeric](10, 2) NULL,

  [margin_percent] [numeric](10, 4) NULL,

  [partner] [nvarchar](50) NULL,

  [datetime_updated] [datetime] NULL,

  [status] [nvarchar](1) NULL

  ) ON [PRIMARY]

  GO

  I even manualy search all the tables, cant find those items. Only when i run the above code.

  Attachments:

  You must be logged in to view attached files.

• Mike01

  SSChampion

  Points: 11413

  More actions

  January 13, 2022 at 8:37 pm

  #3972885

  without any sample data, we can only tell you what we see.  App Category is coming from the label field in app_menu_filters and Menu is coming from the label field in app_menu_filters based on the parentID = uid from App Category value

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/