Creating Breadcrumbs structure from SQL Query

Question

Hi,

I am using SQL Query and below are the tables.

Organization

OrgID    Name        RAOGID   RAOID   SubGroupID
1       Org RAOG   1   NULL       NULL
2       Org RAO         NULL    1     NULL
3       Org Sub Group   NULL       NULL      1

RAOG

RAOGID  AccredID
1              2

RAO

RAOID   RAOGID
1         1

Sub Group

SubGroupID  RAOID
1            1

I have four tables as shown above, I need to make breadcrumb from above table’s structure, so I want to write query which will return the data in below format, please see below.

RAOGID >> RAOID >> SubGroupID

For example from above data my breadcrumb will be

Org RAOG >> Org RAO >> Org Sub Group

The “Organization” table contains all the IDs of ROAG, RAO and SubGroup, I need the query which will return the data in above format.

One more functionality is required. I don't want to show the breadcrumb if there is no subgroupid so my breadcrumb will be Org RAOG >> Org RAO and there is no RAOID then there will be only one breadcrumd i.e. Org RAOG

Thanks, Best Regards, Manoj

Answer 1

You can get this by joining the table to itself a bunch of times with some left joins, like so:

select
    org_roag.name + 
    case when org_roa.id is not null then
        '>>' + org_roa.name +
         case when org_sg.id is not null then
             '>>' + org_sg.name
         else '' end
    else '' end as breadcrumb
from
    organization org_roag
    left join roa on
        org_roag.roagid = roa.roagid
    left join organization org_roa on
        roa.roaid = org_roa.roaid
    left join subgroup sg on
        roa.roaid = sg.roaid
    left join organization org_sg on
        sg.subgroupid = org_sg.subgroupid
where
    org_roag.roagid is not null

Answer 2

Hi Guys,

I solved the above problem for SQL breadcrumb using below query

SELECT     c.Name + ' >> ' + b.Name + '>>' + a.Name AS breadcrumb
FROM         tblOrganisation AS a LEFT OUTER JOIN
                      tblSubGroup AS sg ON a.SubGroupID = sg.SubGroupID LEFT OUTER JOIN
                      tblOrganisation AS b ON sg.RAOID = b.RAOID LEFT OUTER JOIN
                      tblRAO AS rao ON rao.RAOID = b.RAOID LEFT OUTER JOIN
                      tblOrganisation AS c ON c.RAOGID = rao.RAOGID
WHERE     (sg.RAOID IS NOT NULL) AND (a.OrgID = @ORGID)
UNION
SELECT     c.Name + ' >> ' + a.Name AS breadcrumb
FROM         tblOrganisation AS a LEFT OUTER JOIN
                      tblRAO AS rao ON rao.RAOID = a.RAOID LEFT OUTER JOIN
                      tblOrganisation AS c ON c.RAOGID = rao.RAOGID
WHERE     (rao.RAOID IS NOT NULL) AND (a.OrgID = @ORGID)
UNION
SELECT     Name AS breadcrumb
FROM         tblOrganisation AS a
WHERE     (RAOGID IS NOT NULL) AND (OrgID = @ORGID)

Cheers!