Solved

How to use recursive to list parent and child

Posted on 2014-03-12
9
266 Views
Last Modified: 2014-03-12
Hi,

Attached is an image of how I need to display the data.

I have two tables one for the parent Issue and one for the sub Issue.  

 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#'>
	SELECT IssueID, TicketNumberTxt, TitleOfIssue, Status, DateCreated
    FROM LogEntry 
    order by IssueID
</cfquery>


my query for the sub list
<cfquery name = "QSearchResultSub"  datasource='#strDSN#' username='#strUID#' password='#strPWD#'>
	SELECT IssueSubID, IssueID, TicketNumberTxt, TitleOfIssue, Status, DateCreated
    FROM LogEntrySub 
    where IssueID = #QSearchResult.IssueID#
    order by IssueID
</cfquery>

Open in new window



I am not sure how to display it like in the image.

Thanks,
Lululist
0
Comment
Question by:lulu50
  • 6
  • 3
9 Comments
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Hi Lulu,
Its a shame the tickets and the sub tickets are not in the same table.  That would have made this a bit easier and probably less coding on your part to create the two tickets.    Assuming its too late to change that, I would start by putting your two queries together by using a join of the ticket and sub-ticket tables.    Order your query by the issue ID will keep the parent and child records together and make it easier to display in the table.
0
 

Author Comment

by:lulu50
Comment Utility
gdemaria,

it's not too late I can change the database structure

I thought having two tables is easier.

I going to change it to one table just like this:

I added one field called IsParent       to let me know if this record is parent or child


<cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#'>
	SELECT IssueID, IsParent, TicketNumberTxt, TitleOfIssue, Status, DateCreated
    FROM LogEntry 
    order by IssueID
</cfquery>


IssueID	int	Unchecked
UserID	varchar(100)	Checked
IsParent	bit	Checked
CategoryTypeID	int	Checked
TypeIssuetxt	varchar(100)	Checked
TypeIssue	varchar(100)	Checked
DriverTypeID	int	Checked
DriverSubTypeID	int	Checked
DriverSubSubTypeID	int	Checked
ClaimStatusID	int	Checked
ClaimTypeID	int	Checked
ClaimTypeCode	varchar(150)	Checked
TicketNumberID	int	Checked
TicketNumberTxt	varchar(150)	Checked
CommentsResolution	nvarchar(MAX)	Checked
WorkAround	nvarchar(MAX)	Checked
Status	int	Checked
CallVolume	int	Checked
PlannedUnplannedEvent	varchar(200)	Checked
PriorityID	int	Checked
DescriptionOfIssue	varchar(MAX)	Checked
TitleOfIssue	varchar(300)	Checked
LengthOfTime	varchar(50)	Checked
TimeResolved	varchar(50)	Checked
TimeOccurred	varchar(50)	Checked
DateResolved	datetime	Checked
DateIdentified	datetime	Checked
DateCreated	datetime	Checked
		Unchecked

Open in new window


so, the IsParent field will let me know if this issue is a parent issue otherwise it is a child issue.
0
 

Author Comment

by:lulu50
Comment Utility
hmmm,

I thinking about it how can it be in one table if the relationship is one to many?

one parent can have more than one child. so, I do need two tables no?
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
The only extra column you need is parentID which would only be populated on the child issue and it would point to the primary key of the parent issue.   You can use this field instead of the isParent field which would be yes/no.   Using the parentID would tell you if it's a child (because it would be populated) or if its a parent because it would be empty.  It will also tell you which record the parent is as it would hold the ID of the parent record.

I am not sure which field is your primary key is it IssueID?   If so, this new field could be parentIssueID
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:lulu50
Comment Utility
I fixed it

now it looks like this

yes IssueID is the primary key so, the new field I added called ParentIssueID.

I created a few records as child Issue.  How can I display my records?

IssueID	int	Unchecked
UserID	varchar(100)	Checked
ParentIssueID	int	Checked
CategoryTypeID	int	Checked
TypeIssuetxt	varchar(100)	Checked
TypeIssue	varchar(100)	Checked
DriverTypeID	int	Checked
DriverSubTypeID	int	Checked
DriverSubSubTypeID	int	Checked
ClaimStatusID	int	Checked
ClaimTypeID	int	Checked
ClaimTypeCode	varchar(150)	Checked
TicketNumberID	int	Checked
TicketNumberTxt	varchar(150)	Checked
CommentsResolution	nvarchar(MAX)	Checked
WorkAround	nvarchar(MAX)	Checked
Status	int	Checked
CallVolume	int	Checked
PlannedUnplannedEvent	varchar(200)	Checked
PriorityID	int	Checked
DescriptionOfIssue	varchar(MAX)	Checked
TitleOfIssue	varchar(300)	Checked
LengthOfTime	varchar(50)	Checked
TimeResolved	varchar(50)	Checked
TimeOccurred	varchar(50)	Checked
DateResolved	datetime	Checked
DateIdentified	datetime	Checked
DateCreated	datetime	Checked
		Unchecked

Open in new window

0
 

Author Comment

by:lulu50
Comment Utility
How can I display my records to look like the image I attached?

listlist.png
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
Comment Utility
Great, there are a few ways to do it, here is one basic way.   I created a column in the select statement that is the "top" ID of every ticket.  If the ticket is a parent, then it is the issue ID if the ticket is a child, then it is the parent ID.   So this will group parent and children together in the order by clause.     Then all you have to do is indent the children and it's easy to tell the children because the parentIssueID is populated only for children.

<cfquery name = "getLogs"  datasource='#strDSN#' username='#strUID#' password='#strPWD#'>
  SELECT case when parentIssueID is null then IssueId
              else parentIssudeID
		 end  as TopIssueID
		 , IssueID
		 , parentIssueID
		 , TicketNumberTxt
		 , TitleOfIssue
		 , Status
		 , DateCreated
   FROM LogEntry
  order by 1 desc, case when parentIssueID is null then 1 else 2 end
</cfquery>
<!---- the order by above puts all tickets with the same parent issue ID together, then puts the parent on the top of that group ---->

<table>
<cfoutput query="getLogs">
<tr>
 <cfif val(getLogs.parentIssueID)> <!--- if parent issue is populated, this is a child, so indent it --->
   <td style="padding-left:20px;">
 <cfelse>
   <td> <!---- this is a parent, so no indent --->
 </cfif>
   #getLogs.issueID#
 </td>
 <td>#getLogs.ticketNumberTxt#</td>
 <td>#getLogs.TitleOfIssue#</td>
 <td>#getLogs.Status#</td>
</tr>
</cfoutput>
</table>

Open in new window

0
 

Author Comment

by:lulu50
Comment Utility
gdemaria,

Great!! Thank you that's what I want.

Thank you so much,

Lulu

I have a second part for it that I don't know how to do is pagination
I have to open a different question for it.

please, help me again.

Thank you for one million and one time.
0
 

Author Closing Comment

by:lulu50
Comment Utility
Excellent ++++
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now