SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Ordering issue		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

105 Posts
Posted - 02/05/2013 :  07:14:52  Show Profile  Reply with Quote
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

Edited by - tech_1 on 02/06/2013 17:02:10

srimami
Posting Yak Master

152 Posts
Posted - 02/05/2013 :  07:38:47  Show Profile  Reply with Quote
User order by clause in the last with your preference of order like MenuId, ParentId and MenuOrderId
Go to Top of Page

tech_1
Posting Yak Master

105 Posts
Posted - 02/05/2013 :  11:55:02  Show Profile  Reply with Quote
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
Edited by - tech_1 on 02/05/2013 11:55:52
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 02/05/2013 :  12:19:00  Show Profile  Reply with Quote
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
Edited by - Lamprey on 02/05/2013 12:23:34
Go to Top of Page

tech_1
Posting Yak Master

105 Posts
Posted - 02/05/2013 :  12:43:05  Show Profile  Reply with Quote
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
Edited by - tech_1 on 02/05/2013 12:44:06
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 02/05/2013 :  13:18:18  Show Profile  Reply with Quote
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
Go to Top of Page

tech_1
Posting Yak Master

105 Posts
Posted - 02/05/2013 :  14:51:17  Show Profile  Reply with Quote
hmm. OK question: Whats with the Path? :)
Go to Top of Page

tech_1
Posting Yak Master

105 Posts
Posted - 02/05/2013 :  14:55:44  Show Profile  Reply with Quote
it seems to work at a quick glance. thanks! But is there a way without doing this whole path thing?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 02/05/2013 :  15:58:25  Show Profile  Reply with Quote
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.
Edited by - Lamprey on 02/05/2013 16:03:05
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 02/05/2013 :  16:24:13  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 02/05/2013 :  16:29:50  Show Profile  Reply with Quote
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
Go to Top of Page

tech_1
Posting Yak Master

105 Posts
Posted - 02/06/2013 :  16:03:12  Show Profile  Reply with Quote
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
Go to Top of Page

tech_1
Posting Yak Master

105 Posts
Posted - 02/06/2013 :  17:00:10  Show Profile  Reply with Quote
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....
Edited by - tech_1 on 02/06/2013 17:01:05
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000