?
Solved

How to use recursive to list parent and child

Posted on 2014-03-12
9
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 
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

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

719 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