Solved

How to use recursive to list parent and child

Posted on 2014-03-12
9
271 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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 500 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Entering multiple email addresses 4 76
On Submit return to same spot 7 95
CSS HELP 6 91
Can I put javascript code in my application.cfm file 4 48
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 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