Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to use recursive to list parent and child

Posted on 2014-03-12
9
Medium Priority
?
282 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
ID: 39923739
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
ID: 39923770
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
ID: 39923841
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 39

Expert Comment

by:gdemaria
ID: 39923909
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
 

Author Comment

by:lulu50
ID: 39923952
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
ID: 39924094
How can I display my records to look like the image I attached?

listlist.png
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 39924250
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
ID: 39924286
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
ID: 39924290
Excellent ++++
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

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…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
In the video, one can understand the process of resizing images in single or bulk. Kernel Bulk Image Resizer is an easy to use tool for resizing large number of images. One can add and resize multiple images with this tool in single go. The video sh…

607 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