[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Problem

Posted on 2008-11-12
8
Medium Priority
?
160 Views
Last Modified: 2012-05-05
Hi Experts.

It's one of those days, I just cant get this to work as I want it to, can anyone help.

I have a table:
ID      ParentID      Title

1      NULL      Personal, social and emotional development      
2      NULL      Communication, language and literacy            
3      NULL      Problem solving, reasoning and numeracy            
4      NULL      Knowledge and understanding of the world      
5      NULL      Physical development                        
6      NULL      Creative development                        
7      1      Dispositions and attitudes                  
8      1      Social development                        
9      1      Emotional development                        
10      2      Language for communication and thinking            
11      2      Linking sounds and letters      
12      2      Reading                  
13      2      Writing            
14      3      Numbers as labels and counting      
15      3      Calculating
16      3      Shape, space and measure

I want to recreate the menu structure such that I return the title and next to it the relavent sub menus based on ID/Parent ID. This I can do. In addition if the parentID id null (its a top level) and it has no children then it's title appears in the same column as the sub menus. It can appear twice once in the main men and again in the sub ment columns, all I can get is it displays Null, which is it value.

Just to help there should be 6 different main menu headings, and 13 sub menus, 3 of which should be the same as the main menu.

Andy
0
Comment
Question by:Andy Green
  • 4
  • 3
8 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22938499
Is this what you are looking to do?
SELECT m.ID, CASE WHEN p.Title IS NOT NULL THEN p.Title + ' - ' ELSE '' END + m.Title
FROM MenuTable m
LEFT JOIN MenuTable p ON p.ID  = m.ParentID

Open in new window

0
 
LVL 3

Author Comment

by:Andy Green
ID: 22938670
Thanks, but not quite.
I dont need the concatenation, I need them in separate columns, there will be 13 in total with 3 rows, the id, the main menu and sub menu columns
I'll have a play with what you've given me so far.
Andy
0
 
LVL 5

Expert Comment

by:varungd
ID: 22938712
Try this
SELECT parentId, Title from
 ( SELECT p1.ParentID,
          ( SELECT  Title + ','
              FROM TableName  p2
             WHERE p2.parentID = p1.ParentId
               FOR XML PATH('') ) AS Title
  FROM TableName  p1 
      GROUP BY ParentID
 
) A

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:Andy Green
ID: 22938826
Sorry, I should have said 13 rows, 3 colums.
Andy
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22938968
-- select sub menu items with parent information
SELECT m.ID AS MenuID
, p.Title AS MainMenu
, p.Title AS SubMenu
FROM MenuTable m
INNER JOIN MenuTable p ON p.ID  = m.ParentID
UNION ALL -- can use UNION to filter out duplicates
-- join in parent menu with no children
SELECT ID AS MenuID
, Title AS MainMenu
, Title AS SubMenu
FROM MenuTable
WHERE ID NOT IN (SELECT ParentID FROM MenuTable)
AND ParentID IS NULL
0
 
LVL 3

Author Comment

by:Andy Green
ID: 22939187
Still not right - it returns 3 column, but the menu and sub menu are the same.
this sql

SELECT a.AssessmentScale_ID, CASE WHEN b.AssessmentScale_Title IS NOT NULL THEN b.AssessmentScale_Title ELSE '' END , a.AssessmentScale_Title
FROM tbl_AssessmentScale a
LEFT JOIN tbl_AssessmentScale b ON b.AssessmentScale_ID = a.AssessmentScaleParent_ID

Returns this data
1                                                                                           Personal, social and emotional development
2                                                                                          Communication, language and literacy
3                                                                                           Problem solving, reasoning and numeracy
4                                                                                            Knowledge and understanding of the world
5                                                                                           Physical development
6                                                                                           Creative development
7  Personal, social and emotional development          Dispositions and attitudes
8 Personal, social and emotional development           Social development
9 Personal, social and emotional development          Emotional development
10 Communication, language and literacy                  Language for communication and thinking
11 Communication, language and literacy                     Linking sounds and letters
12 Communication, language and literacy                    Reading
13 Communication, language and literacy                    Writing
14 Problem solving, reasoning and numeracy            Numbers as labels and counting
15 Problem solving, reasoning and numeracy           Calculating
16 Problem solving, reasoning and numeracy           Shape, space and measure
This is what I want except - 1,2,& 3 have sub menus so I dont want to see them at the top, and 4, 5 & 6 dont have submenus so I want to see the text in the second colum as the menu title.
Andy
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22939261
That is a type-o, sorry.
-- select sub menu items with parent information
SELECT m.ID AS MenuID
, p.Title AS MainMenu
, m.Title AS SubMenu -- <<< this needed to be m.Title not p.Title
FROM MenuTable m
INNER JOIN MenuTable p ON p.ID  = m.ParentID
UNION ALL -- can use UNION to filter out duplicates
-- join in parent menu with no children
SELECT ID AS MenuID
, Title AS MainMenu
, Title AS SubMenu
FROM MenuTable
WHERE ID NOT IN (SELECT ParentID FROM MenuTable)
AND ParentID IS NULL

Open in new window

0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 31515900
Thank you, I unioned in the first 3 rows and it exactly what I wanted.

Andy
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question