Author |
Topic |
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-05 : 07:14:52
|
Hi there.I am trying to order this by MenuID, then ParentID and then MenuOrderIDany ideas how? this is a menu structure... so I want to display "File" first (MenuID is 1) then all the ParentID's where this it belongs to the "File" Menu, then finally order it by the menuorderID for this group of menu items.Any ideas?quote: SELECT sm.MenuID, sm.MenuOrderID, sm.MenuName, sm.IsAdmin, sm.CustomerAccess, sm.EmployeeAccess, sm.ParentID, sm.CustomerAccess, sm.EmployeeAccess FROM SoftwareMenus sm INNER JOIN UserAccessLevel ual ON ual.MenuID = sm.MenuID INNER JOIN Users u ON u.UserID = ual.UserID INNER JOIN Customer c ON c.CustID = ual.CustomerID WHERE u.UserID = 'tech_1' AND c.CustID = 'ABC123'
Current output:quote: MenuID MenuOrderID MenuName IsAdmin CustomerAccess EmployeeAccess ParentID 6 1 Asset Inquiry NULL 1 1 5 1 1 File NULL 1 1 NULL 91 1 Online Help NULL 1 1 45 92 2 Online FAQ NULL 1 1 45 93 3 How To 0 1 1 45 8 3 Inventory/Control List NULL 1 1 5 10 4 Shipper List NULL 1 1 5 94 4 Recent Updates NULL 1 1 45 95 5 Certificate Explanation NULL 1 1 45 12 5 Upload Storage NULL 1 1 5 83 5 Holidays/Pay Dates 1 0 0 1 86 7 System Library 1 0 0 5 87 8 System Catalog 1 0 0 5 3 8 Change Password NULL 1 1 1 88 9 System Catalog Search 1 0 0 5 89 10 System Request 1 0 0 5 4 10 Exit 0 1 1 1 5 10 Asset NULL 1 1 NULL 90 11 System Request Status 1 0 0 5 14 19 Financial NULL 1 1 27 16 21 Out of Tolerance NULL 1 1 27 27 38 Reporting NULL 1 1 NULL 45 63 Help NULL 1 1 NULL 46 64 About Online NULL 1 1 45 2 2 Software Security 1 0 1 1 47 3 Employee Directory NULL 0 1 1 53 4 HandBook NULL 1 1 1 50 6 News NULL 0 1 1 48 7 Calendar NULL 0 1 1 59 9 Change Account NULL 1 1 1 7 2 Check In/Out NULL 1 1 5 58 6 Upload Data NULL 1 1 5 24 33 Purchase Order NULL 1 1 NULL 26 37 Vendor NULL 1 1 24 35 49 Maintenance NULL 1 1 NULL 36 51 Employee NULL 1 1 35 37 52 Employee Profile NULL 1 1 36 38 53 Employee Titles NULL 1 1 36 40 55 Equipment Due/Return Settings NULL 1 1 35 41 56 Equipment Location List NULL 1 1 35
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-05 : 07:38:47
|
User order by clause in the last with your preference of order like MenuId, ParentId and MenuOrderId |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-05 : 11:55:02
|
huh??When I try ORDER BY MenuID, ParentID, MenuOrderID it does not order it the way I am explaining and expecting :)please run the following script:quote: /****** Object: Table [dbo].[Menus] Script Date: 02/05/2013 16:47:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Menus]( [MenuID] [smallint] NOT NULL, [MenuName] [varchar](48) NOT NULL, [ParentID] [smallint] NULL, [MenuOrderID] [smallint] NULL, [IsAdmin] [bit] NULL, [PageURL] [varchar](100) NULL, [EmployeeAccess] [bit] NOT NULL, [CustomerAccess] [bit] NOT NULL, CONSTRAINT [PK_Menus] PRIMARY KEY NONCLUSTERED ( [MenuID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (1, N'File', NULL, 1, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (2, N'Software Security', 1, 2, 1, N'~/SoftwareSecurity_Qry.aspx', 1, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (4, N'Exit', 1, 10, 0, N'~/LogOff.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (5, N'Asset', NULL, 10, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (6, N'Asset Inquiry', 5, 1, NULL, N'~/Control/StatusEnquiryReport.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (8, N'Inventory/Control List', 5, 3, NULL, N'~/Control/Control.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (9, N'Master Library', 5, 14, NULL, N'~/Control/MLib.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (10, N'Shipper List', 5, 4, NULL, N'~/Control/ShipperList.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (11, N'Cost Saves', 6, 16, NULL, N'~/Report/RptCostSave.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (13, N'Equipment Returned', 5, 18, NULL, N'~/Report/RptEquipReturned.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (14, N'Financial', 27, 19, NULL, N'~/Report/RptFinancial.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (15, N'Online Pricing', 5, 20, NULL, N'~/Report/RptOnlinePricing.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (16, N'Out of Tolerance', 27, 21, NULL, N'~/Report/RptOutOfTolerance.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (17, N'Task Management', NULL, 23, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (18, N'DS/Proc/QA', 17, 25, NULL, N'~/DSProcQA.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (19, N'Equipment Request', 17, 26, NULL, N'~/RequestItems.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (20, N'Onsite Quote Approval', 17, 27, NULL, N'~/ServiceApprovalList.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (21, N'OT Approval', 17, 28, NULL, N'~/QryOTAuthorize.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (22, N'Repair Request', 17, 29, NULL, N'~/QryRepair.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (23, N'Timesheet', 17, 30, NULL, N'~/Employee/QryTimeSheet.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (24, N'Purchase Order', NULL, 33, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (25, N'Purchase Orders', 24, 36, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (26, N'Vendor', 24, 37, NULL, N'~/PurchaseOrder/QryVendor.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (27, N'Reporting', NULL, 38, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (28, N'RptAsset', 27, 39, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (29, N'RptAssetInventoryList', 28, 40, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (30, N'RptAssetCheckInOutHistory', 28, 41, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (31, N'RptTaskManagement', 27, 42, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (32, N'RptTMTimesheet', 31, 43, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (33, N'RptPurchaseOrder', 27, 45, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (34, N'RptPOPOReport', 33, 46, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (35, N'Maintenance', NULL, 49, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (36, N'Employee', 35, 51, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (37, N'Employee Profile', 36, 52, NULL, N'~/Employee/Employee_Qry.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (38, N'Employee Titles', 36, 53, NULL, N'~/Employee/EmpTitle.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (39, N'Training Matrix', 36, 54, NULL, N'~/Employee/QryTrainingMatrix.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (40, N'Equipment Due Cal/Return Settings', 35, 55, NULL, N'~/Control/EquipDueReturnSettings.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (41, N'Equipment Location List', 35, 56, NULL, N'~/Control/Location.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (42, N'Log Issues', 35, 57, NULL, N'~/Maintenance/QryLogMProIssues.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (43, N'Quality Feedback', 35, 59, NULL, N'~/QryFeedback.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (44, N'Non Asset Rpr Tracking', 35, 60, NULL, N'~/Maintenance/NonAssetRepairTracking_Qry.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (45, N'Help', NULL, 63, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (46, N'About Online', 45, 64, NULL, N'~/Help/About.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (47, N' Employee Directory', 1, 3, NULL, N'~/Employee/EmployeeDirectory.aspx', 1, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (48, N'Calendar', 1, 7, NULL, N'~/Calender/CalenderView.aspx', 1, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (49, N'Change Request', 35, 50, NULL, N'~/Maintenance/ChangeRequest_Qry.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (50, N' News', 1, 6, NULL, N'~/Calendar/News_List.aspx', 1, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (51, N'Inventory', 24, 34, NULL, N'~/PurchaseOrder/Inventory_Qry.aspx', 1, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (52, N'Parts Order', 24, 35, NULL, N'~/PurchaseOrder/PartsOrder_Qry.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (53, N' HandBook', 1, 4, NULL, N'~/Docs/Handbook.pdf', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (55, N'Rpt3rdPartyServices', 27, 48, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (56, N'Process Improvement Ideas', 35, 58, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (57, N'Winning Scenarios', 35, 61, NULL, N'~/Maintenance/WinningScenarios_Qry.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (58, N'Upload Data', 5, 6, NULL, N'~/Control/UploadData.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (59, N'Change Account', 1, 9, NULL, N'~/PasswordSettings/ChangeAcct.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (60, N'RptTMRepairRequest', 31, 44, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (61, N'Workorder', 17, 31, NULL, N'~/WorkOrder_Qry.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (62, N'RptPOPOSummaryChart', 33, 47, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (63, N'Cost Centers', 35, 62, NULL, N'~/Maintenance/CostCenters_Qry.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (64, N'ISR', 17, 32, NULL, N'~/ISR/ISR_Qry.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (65, N'Asset Utilization', 27, 65, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (66, N'TSO Tracking', 27, 66, NULL, NULL, 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (85, N'Inventory/Control List', 5, 3, 1, N'~/Control/Control.aspx', 0, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (86, N'System Library', 5, 7, 1, N'~/Reserve/SystemLibraryList.aspx', 0, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (87, N'System Catalog', 5, 8, 1, N'~/Reserve/SlInventoryList.aspx', 0, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (88, N'System Catalog Search', 5, 9, 1, N'~/Reserve/SliSearch.aspx?strAction=1', 0, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (89, N'System Request', 5, 10, 1, N'~/Reserve/SlRequest.aspx', 0, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (90, N'System Request Status', 5, 11, 1, N'~/Reserve/SlReserveList.aspx', 0, 0)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (91, N' Online Help', 45, 1, NULL, N'~/Help/HelpMain.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (92, N' Online FAQ', 45, 2, NULL, N'~/Help/_FAQ.PDF', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (93, N'How To', 45, 3, 0, N'~/Help/HowTo.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (94, N'Recent Updates', 45, 4, NULL, N'~/Help/RecentUpdates.aspx', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (95, N'Certificate Explanation', 45, 5, NULL, N'~/Help/CERT.PDF', 1, 1)INSERT [dbo].[Menus] ([MenuID], [MenuName], [ParentID], [MenuOrderID], [IsAdmin], [PageURL], [EmployeeAccess], [CustomerAccess]) VALUES (3, N'Change Password', 1, 8, NULL, N'~/PasswordSettings/ChangePassAdmin.aspx', 1, 1)/****** Object: Default [DF__Menus__Emplo__373BFD7F] Script Date: 02/05/2013 16:47:49 ******/ALTER TABLE [dbo].[Menus] ADD DEFAULT ((1)) FOR [EmployeeAccess]GO/****** Object: Default [DF__Menus__Custo__383021B8] Script Date: 02/05/2013 16:47:49 ******/ALTER TABLE [dbo].[Menus] ADD DEFAULT ((1)) FOR [CustomerAccess]GO
Then simply do for example SELECT * FROM MenusI want it ordered by the parent then parentID and finally for menuorderidSo everything for MenuID 1 should be in order (where MenuID = 1 and parentID = NULL AND parentID = 1 so the "File" menu is shown along with its children) - and of course for each of the parent menus and their children. when ParentID is null, this means that the menu record is the parent and not a child |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-05 : 12:19:00
|
I'm nto very clear on what you want. Given your sample data can you tell us what you want for output?I'm guessing you want a CTE of some sort, but here is a simple order by that might help...?SELECT *FROM MenusORDER BY COALESCE(ParentID, MenuID), MenuOrderID |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-05 : 12:43:05
|
yes, I am guessing a CTE.so this is what I want:quote: File Child1 Child2 Child3Asset Child1 Child2Maintainence Child1 Child2 Child3 SubChild1 SubChild2
and so on.EDIT: damn, formatting no good! Maintainence should have 3 children, then 2 subchildren which belong to "Child3"So we have the parent menus (where ParentID IS NULL) then for each one of these, underneath, want their children "attached" to the parent.makes sense? Like a tree hierarchy |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-05 : 13:18:18
|
Maybe this will work for ya:;WITH cteTree AS(SELECT * ,CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), MenuID), 12) AS VARCHAR(MAX)) AS PathFROM MenusWHERE ParentID IS NULLUNION ALLSELECT Child.* ,Parent.Path + RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), Child.MenuID), 12) AS PathFROM Menus AS ChildINNER JOIN cteTree AS Parent ON Child.ParentID = Parent.MenuID)SELECT *FROM cteTree ORDER BY Path |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-05 : 14:51:17
|
hmm. OK question: Whats with the Path? :) |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-05 : 14:55:44
|
it seems to work at a quick glance. thanks! But is there a way without doing this whole path thing? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-05 : 15:58:25
|
The Path is somestimes called a materialized path. It basially makes a string that SQL can use to help sort the set since the CTE won't order the way you want. EDIT: I see that you metioned the OrderID in your fiest post. I'll see if I can get that in there as well. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-05 : 16:24:13
|
I tweaked the Path a bit so it could be used in conjuction with the MenuOrderID column to get the sort order you wanted. ;WITH cteTree AS(SELECT * ,CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), MenuID), 12) AS VARCHAR(MAX)) AS PathFROM MenusWHERE ParentID IS NULLUNION ALLSELECT Child.* ,Parent.Path + COALESCE(CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), Parent.MenuID), 12) AS VARCHAR(MAX)), '') AS PathFROM Menus AS ChildINNER JOIN cteTree AS Parent ON Child.ParentID = Parent.MenuID)SELECT *FROM cteTree ORDER BY Path, MenuOrderID |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-05 : 16:29:50
|
Heck, I guess you only need to use the MenuOrderID to build a Path to sort by:;WITH cteTree AS(SELECT * ,CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), MenuOrderID), 12) AS VARCHAR(MAX)) AS PathFROM MenusWHERE ParentID IS NULLUNION ALLSELECT Child.* ,Parent.Path + COALESCE(CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), Child.MenuOrderID), 12) AS VARCHAR(MAX)), '') AS PathFROM Menus AS ChildINNER JOIN cteTree AS Parent ON Child.ParentID = Parent.MenuID)SELECT *FROM cteTree ORDER BY Path |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-06 : 16:03:12
|
ok so now I am executing this/altered it to my tables but not good at all. Performance is slow as heck - something ive obviously done wrong.here is my original query using multiple tables and a WHERE clause. How can I convert the following to the above?quote: SELECT sm.MenuID, sm.MenuOrderID, sm.MenuName, sm.PageURL, sm.ParentID FROM SoftwareMenus sm INNER JOIN UserAccessLevel ual ON ual.MenuID = sm.MenuID INNER JOIN Users u ON u.UserID = ual.UserID INNER JOIN Customer c ON c.CustID = ual.CustomerID WHERE u.UserID = @userID AND c.CustID = @custID
|
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-06 : 17:00:10
|
ok, managed to convert it. seems ok at a first glance but the performance is a little slow. Any ideas how to speed it up?final query:quote: ;WITH cteTree AS(SELECT sm.MenuID, sm.MenuOrderID, sm.MenuName, sm.IsAdmin, sm.CustomerAccess, sm.EmployeeAccess, sm.PageURL, sm.ParentID ,CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), sm.MenuOrderID), 12) AS VARCHAR(MAX)) AS [Path] FROM SoftwareMenus smWHERE sm.ParentID IS NULLUNION ALLSELECT Child.MenuID, Child.MenuOrderID, Child.MenuName, Child.IsAdmin, Child.CustomerAccess, Child.EmployeeAccess, Child.PageURL, Child.ParentID ,Parent.[Path] + COALESCE(CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), Child.MenuOrderID), 12) AS VARCHAR(MAX)), '') AS [Path]FROM SoftwareMenus AS ChildINNER JOIN cteTree AS Parent ON Child.ParentID = Parent.MenuID)SELECT sm.MenuID, sm.MenuOrderID, sm.MenuName, sm.IsAdmin, sm.CustomerAccess, sm.EmployeeAccess, sm.PageURL, sm.ParentIDFROM cteTree sm INNER JOIN UserAccessLevel ual ON ual.MenuID = sm.MenuID INNER JOIN Users u ON u.UserID = ual.UserID INNER JOIN Customer c ON c.CustID = ual.CustomerID WHERE u.UserID = @userID AND c.CustID = @custIDORDER BY sm.[Path]
so for 41 records, takes 1-2 second to come back.... |
|
|
|
|
|