Ordering issue - 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 2008 Forums

Transact-SQL (2008)

Ordering issue

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 MenuOrderID

any 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]
GO
SET ANSI_PADDING OFF
GO
INSERT [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 Menus

I want it ordered by the parent then parentID and finally for menuorderid

So 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 Menus
ORDER 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
Child3
Asset
Child1
Child2
Maintainence
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 Path
FROM 
	Menus
WHERE
	ParentID IS NULL

UNION ALL

SELECT 
	Child.*
	,Parent.Path + RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), Child.MenuID), 12) AS Path
FROM
	Menus AS Child
INNER 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 Path
FROM 
	Menus
WHERE
	ParentID IS NULL

UNION ALL

SELECT 
	Child.*
	,Parent.Path + COALESCE(CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), Parent.MenuID), 12) AS VARCHAR(MAX)), '') AS Path
FROM
	Menus AS Child
INNER 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 Path
FROM 
	Menus
WHERE
	ParentID IS NULL

UNION ALL

SELECT 
	Child.*
	,Parent.Path + COALESCE(CAST(RIGHT(REPLICATE('0', 12) + CONVERT(VARCHAR(12), Child.MenuOrderID), 12) AS VARCHAR(MAX)), '') AS Path
FROM
	Menus AS Child
INNER 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 sm
WHERE
sm.ParentID IS NULL

UNION ALL

SELECT
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 Child
INNER 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.ParentID
FROM 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 = @custID
ORDER BY sm.[Path]

so for 41 records, takes 1-2 second to come back....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources