Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to use recursive to list parent and child

Posted on 2014-03-12
9
Medium Priority
?
279 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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 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