Creating Breadcrumbs structure from SQL Query

Question

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

Hi Dems, Thanks for your reply. What type of example you need, I have given table structure above and the output which i need is Org RAOG >> Org RAO >> Org Sub Group and if there is no "Sub Group" then the breadcrumb will have Org RAOG >> Org RAO and same with RAO if there is no RAO then breadcrubs will be only Org RAOG — Manoj Singh
– Manoj Singh, Commented Aug 24, 2009 at 9:43
I have below query with me I want to modify it according above conditions. SELECT ISNULL(root.name, '') +' >> '+ ISNULL(mid.name, '') +' >> '+ ISNULL(sub.name, '') FROM tblOrganisation sub INNER JOIN tblSubGroup sg ON sg.subgroupid = sub.subgroupid INNER JOIN tblRAO r ON r.raoid = sg.raoid INNER JOIN tblOrganisation mid ON mid.raoid = r.raoid INNER JOIN tblOrganisation root ON root.raogid = r.raogid — Manoj Singh
– Manoj Singh, Commented Aug 24, 2009 at 9:50
There seems to be something a bit wrong with the data model. If the relationship between RAOG, RAO and SubGroup is already specified in these tables, why do you need Organization? Can you specify PKs, FKs and any constraints so that the data model is clearer. — Andrew G
– Andrew G, Commented Aug 24, 2009 at 11:32

Manoj Singh · Accepted Answer · 2009-08-26 05:03:17Z

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!

Eric · Accepted Answer · 2009-08-24 11:18:19Z

0

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

answered Aug 24, 2009 at 11:18

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Collectives™ on Stack Overflow

Creating Breadcrumbs structure from SQL Query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related