Andy Green
asked on
SQL Problem
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
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
ASKER
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
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
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
ASKER
Sorry, I should have said 13 rows, 3 colums.
Andy
Andy
-- 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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, I unioned in the first 3 rows and it exactly what I wanted.
Andy
Andy
Open in new window