Include ParentId records in SQL

Question

I have the below table that contains the Application Feature List:

ApplicationFeature

Id Name MenuId

Values are:

1 PatientSearch 2

ApplicationMenu

Id DisplayText Url ParentMenuId

Values are

1 Patient patient NULL

2 PatientSearch search 1

I need to fetch the menu information along with the features. So far I have only been able to get the mapped menu but not the parent menu item as well.

SELECT
AF.Name, AF.Id As FeatureId, AM.Id AS MenuId, AM.DisplayText,AM.Url
FROM 
ApplicationFeature AF 
INNER JOIN ApplicationMeny AM ON AF.MenuId=AM.Id

My results only show the PatientSearch menu details but don't include the Patient Menu.

How can I cause the query to retrieve parent menu detail too?

Edit:

I need to get build the menu with Parent. In case a parent menu has another parent I need to include that menu also.

-- Parent(Id=2)

  --- SubParent(Id=10 & ParentId=2)

    ----ActualMenuMappedwithFeature(Id=30 & ParentId=10)

In the above case I want result with menu ids 30, 10, 2

Can you add a "correct" result to your post? – Gidil Aug 02 '13 at 13:34 — Gidil, Aug 02 '13 at 13:34
pls show correct results – Roman Pekar Aug 02 '13 at 13:55 — Roman Pekar, Aug 02 '13 at 13:55

Roman Pekar · Answer 1 · 2013-08-02T14:50:08.267

Well It's hard to say, what you want to get. But if you want to fetch only one parent, you can do additional join

select
    AF.Name, AF.Id As FeatureId,
    AM.Id as MenuId, AM.DisplayText, AM.Url,
    AMP.Id as ParentMenuId, AMP.DisplayText as ParentDisplayText, AM.Url as ParentUrl
from ApplicationFeature as AF
    inner join ApplicationMenu as AM on AM.Id = AF.MenuId
    left outer join ApplicationMenu as AMP on AMP.Id = AM.ParentMenuId

If you want to get all parents up to the top, please clarify desired output format
EDIT:

declare @FeatureId = ???

;with CTE as (
  select
     AF.Name, AF.Id As FeatureId,
     AM.Id as MenuId, AM.DisplayText, AM.Url,
     AM.ParentId
  from ApplicationFeature as AF
     inner join ApplicationMenu as AM on AM.Id = AF.MenuId
  where AF.Id = @FeatureId

  union all

  select
     null as Name, null as FeatureId,
     AM.Id as MenuId, AM.DisplayText, AM.Url,
     AM.ParentId
  from ApplicationMenu as AM
     inner join CTE as C on C.ParentId = AM.Id
)
select * from CTE

edited answer, still don't get why do you need structure like this, but anyway, check is out, may be I misunderstood you — Roman Pekar, Aug 02 '13 at 14:50

Anonymoose · Answer 2 · 2013-08-02T13:56:38.897

-1

UPDATED

You need to join a second time to get the parent

SELECT AF.ID,
AM.DisplayText as child,
Parent.DisplayText as parent
FROM ApplicationFeature AF 
LEFT JOIN ApplicationMenu AM ON AF.MenuId = AM.Id 
LEFT JOIN ApplicationMenu as Parent on AM.ParentMenuId = Parent.ID

SQLFiddle

RESULT

enter image description here

edited Aug 02 '13 at 13:56

answered Aug 02 '13 at 13:41

Anonymoose

2,389
6
36
69

That query does not return the parent: see this [fiddle](http://sqlfiddle.com/#!3/34b5e/4) – Filipe Silva Aug 02 '13 at 13:46
yes it does, SQLFiddle doesnt show correctly. make it SELECT Parent.* and it will work. – Anonymoose Aug 02 '13 at 13:47
i'm not following you. – Filipe Silva Aug 02 '13 at 13:49
SQLFiddle was not showing the parent because the column names were not unique. Both 'AM' and 'Parent' have the same column names so SQLFiddle only shows them once. Check my Fiddle link – Anonymoose Aug 02 '13 at 13:50
Maybe i understood the question wrong. i thought he wanted both records, but i don't know. – Filipe Silva Aug 02 '13 at 13:57

Include ParentId records in SQL

ApplicationFeature

ApplicationMenu

2 Answers2