mardi 8 décembre 2015

Delete duplicate Mkey from the query

I have a query below

SELECT DISTINCT a.mkey, 0 child_menu_mkey, UPPER(a.menu_name) parent_menu, '' child_menu 
FROM wms_menu_hdr a,
     wms_menu_hdr b 
WHERE a.mkey = b.parent_mkey 
UNION 
SELECT DISTINCT b.mkey, b.mkey child_menu_mkey, '' parent_menu, b.menu_name child_menu 
FROM wms_menu_hdr a,
     wms_menu_hdr b 
WHERE a.mkey = b.parent_mkey 
ORDER BY 1,2 

which displays me results as

mkey  child_menu_mkey     parent_menu                  child_menu

1       0                ADMIN  
2       0                PUT AWAY PROCESS       
3       0               PICK UP PROCESS 
4       0               STORAGE PROCESS 
5       0                DAMAGED STOCK  
5       5                                            Damaged Stock
6       0                MASTERS        
6       6                                             Masters
8       8                                            Put Away Details
9       9                                            Put Away Allocation Bin
10      10                                          Put Away Confirmation
11      11                                            Put Away Report
12      12                                          Pick Up Details
13      13                                           Pick Up Find Storage Bin
14      14                                           Pick Up Confirmation

How to delete duplicate mkey now ?

I am using SQL-server-2005

UPDATE

I want the result to be displayed as like below,

mkey  child_menu_mkey     parent_menu                  child_menu
1       0                      ADMIN    
1       6                                                          Masters
2       0                   PUT AWAY PROCESS    
2       8                                                  Put Away Details
2       9                                                 Put Away Allocation Bin
2       10                                                Put Away Confirmation
2       11                                                Put Away Report

Aucun commentaire:

Enregistrer un commentaire