Solved

Search criteria

Posted on 2014-03-18
17
245 Views
Last Modified: 2014-03-20
Hi,

I have a list for a parent and child relationship

I am doing the search criteria right now and not sure
how to do this:

If I search for the parent issue ID 36
I should get in my result the parent issue ID 36 along with the children records

If I search for the child issue ID 54
I should get in my result the parent issue ID and the child ID 54

This is what I have so far.

Thank you for your help.
lulu

 
<cfcase value="4"> 
       AND L.IssueID LIKE '%#CookieSearchField#%' or L.ParentIssueID LIKE '%#CookieSearchField#%' 
    </cfcase> 

Open in new window



<cfif isdefined("FORM.TechType") OR isdefined("URL.page") >
 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="r">
  SELECT case when L.ParentIssueID is null then L.IssueId
              else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
		 , L.ParentIssueID
		 , L.TicketNumberTxt
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
   FROM LogEntry L
   LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
   
    <CFIF IsDefined("Cookie.CookieTechType")>
   	   WHERE   L.CategoryTypeID = #Cookie.CookieTechType#
   
    <CFIF IsDefined("Cookie.CookieStatus") && #Trim(Cookie.CookieStatus)# neq "1">
       AND L.StatusID = #Trim(Cookie.CookieStatus)#
	</CFIF>
    
     <CFIF IsDefined("Cookie.CookieStatus") && #Trim(Cookie.CookieStatus)# neq "1">
       AND L.DateCreated between '#Trim(Cookie.CookieDateFrom)#' and '#Trim(Cookie.CookieGetDateTo)#' 
     </CFIF>
   
   <CFIF IsDefined("Cookie.CookieSearchBy")>
   
   <cfswitch expression="#Trim(Cookie.CookieSearchBy)#"> 
    <cfcase value="2"> 
      
    </cfcase> 
    <cfcase value="3"> 
      
    </cfcase> 
    <cfcase value="4"> 
       AND L.IssueID LIKE '%#CookieSearchField#%' or L.ParentIssueID LIKE '%#CookieSearchField#%' 
    </cfcase> 
    <cfcase value="5"> 
     
    </cfcase> 
    <cfcase value="6"> 
    	<!---AND L.IssueID LIKE '%#CookieSearchField#%' or L.ParentIssueID LIKE '%#CookieSearchField#%'  --->
    </cfcase> 
    <cfdefaultcase> 
       
    </cfdefaultcase> 
</cfswitch> 

   </CFIF>
   
   </CFIF>
   
  order by 1 desc, case when L.parentIssueID is null then 1 else 2 end
</cfquery>
</cfif>

Open in new window



parentchild
parentSQL ChildSqL
0
Comment
Question by:lulu50
  • 9
  • 6
  • 2
17 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39937357
As I understand it, everything's working UNTIL your bottom example.  Right?  

What is the CategoryTypeID of the record the last query misses?

As I understand the operator precedence, your query is operating as though these parentheses are in place:

WHERE (L.CategoryTypeID = 1 AND L.IssueID like '%55%') OR L.ParentIssueID like '%55%'

If that is not your intention or there is any question, put in the parentheses to group your conditions in the manner your intend.
0
 

Author Comment

by:lulu50
ID: 39937391
Hi Daniel,

Yes, everything works fine but what I want when I search for a child I also need the parent to be listed as well.

CategoryTypeID should not be in parentheses with Issue ID

SELECT case when L.ParentIssueID is null then L.IssueId else 
L.ParentIssueID end as TopIssueID , 
 L.IssueID , L.ParentIssueID , 
L.TicketNumberTxt , L.TitleOfIssue , I.Status , L.DateCreated FROM 
LogEntry L LEFT JOIN IssueStatus I on I.StatusID = L.StatusID WHERE 
L.CategoryTypeID = 1 AND (L.IssueID LIKE '%55%' or L.ParentIssueID 
= L.IssueID) 
 order by 1 desc, case when L.parentIssueID is null 
then 1 else 2 end 

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39937585
And ... does placing the parentheses like you've shown correct anything?

Is the CategoryTypeID of the omitted record 1?
0
 

Author Comment

by:lulu50
ID: 39938328
no IssueID is the primary key

CategoryTypeID is used just to filter the data.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39939400
Hi Lulu,
There are a couple ways to do this.  

Here is one way that involves two queries.  Other ways may involve joining two tables or using a UNION to combine tables.

Option 1 - do it in two queries.   The first query use the WHERE clause to filter and get only the records that you want to get from the search.   In your SELECT clause, select the issueID and the parentIssueID.    Then you can make a quick list of these IDs using this..

<cfset FoundIDs = valueList(myQuery.parentIssueID)>
<cfset FoundIDs = listAppend(FoundIDs, myQueryIssueID)>

Now your variable FoundIDs has all parent and child IDs of records found.

Then just do another query to fetch all needed columns for these records:

where   IssueID in (#foundIDs#)
0
 

Author Comment

by:lulu50
ID: 39939434
oh, gdemaria

good to hear back from you!!!!!!!  

That's exactly what I was thinking of doing.

I will try it and let you know soon.
0
 

Author Comment

by:lulu50
ID: 39940090
gdemaria,

I created a new query, but I don't know how to get them both if my search value only matches with the child issueID -> not sure how to get them both. or how to do the query to get them both if my search has matched the child

this is what I have:

<cfif  isdefined("Cookie.CookieSearchBy") && #Cookie.CookieSearchBy# eq 4>
 <cfquery name = "QsubResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="d">
	
SELECT case when L.ParentIssueID is null then L.IssueId 
else L.ParentIssueID end as TopIssueID , L.IssueID , 
L.ParentIssueID , L.TicketNumberTxt , L.TitleOfIssue , 
I.Status , L.DateCreated , C.Category, L.UserID FROM LogEntry L 
LEFT JOIN IssueStatus I on I.StatusID = L.StatusID 
LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID 
where 
IssueID = 77 case when ParentIssueID = 77 then issueID end
?
 order by 1 desc, case when L.parentIssueID is null then 1 else 2 end 

</cfquery>
</cfif>


Not sure how to do this "In your SELECT clause, select the issueID and the parentIssueID.    Then you can make a quick list of these IDs using this.."



<cfif isdefined("FORM.TechType") OR isdefined("URL.page") >
 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="r">
  SELECT case when L.ParentIssueID is null then L.IssueId
         else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
		 , L.ParentIssueID
		 , L.TicketNumberTxt
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
         , C.Category
   FROM LogEntry L
   LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
   LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
   where L.IssueID <> ''

   <CFIF IsDefined("Cookie.CookieSearchBy")>
   
   <cfswitch expression="#Trim(Cookie.CookieSearchBy)#"> 

    <cfcase value="4"> 
      AND L.IssueID = #trim(CookieSearchField)# or L.ParentIssueID = #trim(CookieSearchField)# 
    </cfcase> 

    <cfdefaultcase> 
       
    </cfdefaultcase> 
</cfswitch> 

   </CFIF>

  order by 1 desc, case when L.parentIssueID is null then 1 else 2 end
</cfquery>
</cfif>

Open in new window

0
 

Author Comment

by:lulu50
ID: 39940418
How can I apply a distinct to this query?

If I do select distinct ....

I get error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

  SELECT case when L.ParentIssueID is null then L.IssueId else 
  L.ParentIssueID end as TopIssueID , L.IssueID , L.ParentIssueID , 
  L.TicketNumberTxt , L.TitleOfIssue , I.Status , L.DateCreated ,
   C.Category FROM LogEntry L LEFT JOIN IssueStatus I on I.StatusID = L.StatusID 
   LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID 
   LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID
    LEFT JOIN SelectedDepartment SD on SD.IssueID = L.IssueID 
    LEFT JOIN DepartmentImpacted DI on 
    DI.DepartmentImpactedID = SD.DepartmentImpactedID 
    where L.IssueID <> '' AND L.DateCreated between '03/19/2014' and '03/19/2014'
     order by 1 desc, case when L.parentIssueID is null then 1 else 2 end 

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:gdemaria
ID: 39940520
Ok, I'm back... the first query is supposed to have your where clause and filter all your search criteria.   Is that your first query shown?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39940535
This is what I was envisioning...


 <!--- find all the matching records, no need to use ORDER BY here, the order of this fetch does not matter,
       you will order the records on the next query ---->

 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="r">
  SELECT   L.IssueID
		 , L.ParentIssueID
   FROM LogEntry L
      LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
      LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
   where 1 = 1
   and   L.DateCreated between '03/19/2014' and '03/19/2014'

   <!---- add all your where clause filters here below ---->
   <cfif IsDefined("Cookie.CookieSearchBy")>
   
	 <cfswitch expression="#Trim(Cookie.CookieSearchBy)#"> 
	    <cfcase value="4"> 
	      AND L.IssueID = #trim(CookieSearchField)# or L.ParentIssueID = #trim(CookieSearchField)# 
	    </cfcase> 
	    <cfdefaultcase> 
	       
	    </cfdefaultcase> 
	</cfswitch> 
   </cfif>
 </cfquery>
 
<cfset FoundIssueIDs = valueList(QSearchResult.IssueID)>
<cfset FoundIssueIDs = listAppend(FoundIssueIDs, QSearchResult.ParentIssueID)>

<!--- Now FoundIssueIDs is a comma delimited list of all IssueIDs and ParentIssueIDs of any ticket that matched the criteria
      Yes, this variable will hold many duplicate values, but that's Ok, the select will only fetch each record ONCE 
      ---->

 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="r">
  SELECT case when L.ParentIssueID is null then L.IssueId
         else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
		 , L.ParentIssueID
		 , L.TicketNumberTxt
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
         , C.Category
   FROM LogEntry L
     LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
     LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
  <cfif len(FoundIssueIDs) eq 0>
   where 1 = 2 --- there is nothing on the IDs list, so do not return any records
  <cfelse>
   where L.IssueID in (#FoundIssueIDs#)
  </cfif>
  order by 1 desc, case when L.parentIssueID is null then 1 else 2 end 
</cfquery>

Open in new window

0
 

Author Comment

by:lulu50
ID: 39942160
gdemaria,

one error:

one how can I remove the extra comma at the end?
(75,76,77,78,79,80,81,82,83,)

In coldfusion can we have two Query names with the same name.?
name = "QSearchResult"

SELECT case when L.ParentIssueID is null 
then L.IssueId else L.ParentIssueID end as TopIssueID , 
L.IssueID , L.ParentIssueID , L.TicketNumberTxt , L.TitleOfIssue , 
I.Status , L.DateCreated , C.Category FROM LogEntry L 
LEFT JOIN IssueStatus I 
on I.StatusID = L.StatusID LEFT JOIN CategoryType C 
on C.CategoryTypeID = L.CategoryTypeID 
where L.IssueID in (75,76,77,78,79,80,81,82,83,) 
order by 1 desc, case when L.parentIssueID is null then 1 else 2 end 

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39942218
> one how can I remove the extra comma at the end?
(75,76,77,78,79,80,81,82,83,)

Hmmm, I guess that happens because the parentIDs can be empty and although ColdFusion can ignore the empty spots, SQL does not like them.   So, instead, let's just do a quick loop to create a clean list with all unique values (no duplicates, no empty)...

<cfset FoundIssueIDs = "">
<cfloop index="kk" list="#valueList(QSearchResult.IssueID)#,#valueList(QSearchResult.ParentIssueID)#">
  <cfif listFind(FoundIssueIDs,kk) eq 0>
     <cfset FoundIssueIDs = listAppend(FoundIssueIDs,kk)>
  </cfif>
</cfloop>

Open in new window



> In coldfusion can we have two Query names with the same name.?
name = "QSearchResult"

You can, because it is just like writing over a variable that you already used.   The first query will no longer be in QSearchResult once the second query is defined.   So the first one is over written.    To be cleaner, we should probably give them different names.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39942250
I forgot to mention that cfloop code block I provided should go after the first query and replace the two lines:

<cfset FoundIssueIDs = valueList(QSearchResult.IssueID)>
<cfset FoundIssueIDs = listAppend(FoundIssueIDs, QSearchResult.ParentIssueID)>
0
 

Author Comment

by:lulu50
ID: 39942288
gdemaria,

I am getting some duplicate because of my left join on the department
SelectedDepartment and DepartmentImpacted those two table if remove them
I don't have duplicate but I need them for the search criteria.
What should I do :-(

<!--- find all the matching records ---->

 <cfquery name = "QSearchResultCriteria"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="r">
  SELECT distinct  L.IssueID
		 , L.ParentIssueID
   FROM LogEntry L
      LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
      LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
      LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID
      LEFT JOIN SelectedDepartment SD on SD.IssueID = L.IssueID 
      LEFT JOIN DepartmentImpacted DI on DI.DepartmentImpactedID = SD.DepartmentImpactedID 
   where 1 = 1
   
    <CFIF IsDefined("Cookie.CookieDateFrom") && IsDefined("Cookie.CookieGetDateTo")>
   		AND L.DateCreated between #Trim(Cookie.CookieDateFrom)# and #Trim(Cookie.CookieGetDateTo)#
	</CFIF>
    
    <CFIF IsDefined("Cookie.CookieStatus") && #Trim(Cookie.CookieStatus)# neq "1">
       AND L.StatusID = #Trim(Cookie.CookieStatus)#
	</CFIF>
    
   <!---- add all your where clause filters here below ---->
   <cfif IsDefined("Cookie.CookieSearchBy")>
   
	 <cfswitch expression="#Trim(Cookie.CookieSearchBy)#"> 
        <cfcase value="2"> 
  			AND DI.Department like '%#Cookie.CookieSearchField#%'
    </cfcase> 
    <cfcase value="4"> 
      		AND L.IssueID = #trim(CookieSearchField)# or L.ParentIssueID = #trim(CookieSearchField)# 
    </cfcase> 
    <cfcase value="6"> 
      		AND T.TicketNumber like '%#Cookie.CookieSearchField#%'
    </cfcase> 
    <cfcase value="8"> 
    		AND L.UserID LIKE '%#Cookie.CookieSearchField#%' 
    </cfcase> 
	    <cfdefaultcase> 
	       
	    </cfdefaultcase> 
	</cfswitch> 
   </cfif>
 </cfquery>

<cfset FoundIssueIDs = "">
<cfloop index="kk" list="#valueList(QSearchResultCriteria.IssueID)#,#valueList(QSearchResultCriteria.ParentIssueID)#">
  <cfif listFind(FoundIssueIDs,kk) eq 0>
     <cfset FoundIssueIDs = listAppend(FoundIssueIDs,kk)>
  </cfif>
</cfloop>

<!--- Now FoundIssueIDs is a comma delimited list of all IssueIDs and ParentIssueIDs of any ticket that matched the criteria
      Yes, this variable will hold many duplicate values, but that's Ok, the select will only fetch each record ONCE 
      ---->

 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="r">
  SELECT case when L.ParentIssueID is null then L.IssueId
         else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
		 , L.ParentIssueID
		 , L.TicketNumberTxt
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
         , C.Category
   FROM LogEntry L
     LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
     LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
     LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID
     LEFT JOIN SelectedDepartment SD on SD.IssueID = L.IssueID 
     LEFT JOIN DepartmentImpacted DI on DI.DepartmentImpactedID = SD.DepartmentImpactedID 
  <cfif len(FoundIssueIDs) eq 0>
   where 1 = 2 
  <cfelse>
   where L.IssueID in  (#FoundIssueIDs#)
  </cfif>
  order by 1 desc, case when L.parentIssueID is null then 1 else 2 end 
</cfquery>

Open in new window



SELECT case when L.ParentIssueID is null then L.IssueId 
else L.ParentIssueID end as TopIssueID , L.IssueID , L.ParentIssueID ,
 L.TicketNumberTxt , L.TitleOfIssue , I.Status , L.DateCreated , C.Category 
 FROM LogEntry L LEFT JOIN IssueStatus I on I.StatusID = L.StatusID 
 LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID 
 LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID 
 --LEFT JOIN SelectedDepartment SD on SD.IssueID = L.IssueID 
 --LEFT JOIN DepartmentImpacted DI on DI.DepartmentImpactedID = SD.DepartmentImpactedID 
 where L.IssueID in (75,76,77,78,79,80,81,82,83) 
 order by 1 desc, case when L.parentIssueID is null then 1 else 2 end 

Open in new window

0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 39942376
Ok, so a bunch of questions about that...

First, let's check the JOIN CLAUSE..
I assume this is correct, but just wanted to make sure.  You are joining Selected Departments with the LogEntry table and then joining DepartmentImpacted to SelectedDepartment (NOT to Log Entry).   Just want to verify..
     LEFT JOIN SelectedDepartment SD on SD.IssueID = L.IssueID 
     LEFT JOIN DepartmentImpacted DI on DI.DepartmentImpactedID = SD.DepartmentImpactedID 

Open in new window


Now the SELECT clause..
You are not selecting any column from SelectedDepartment or from DepartmentImpacted.   Or even from Issue Status.   If you don't select from a table, there seems to be no reason to include that table in the JOIN Clause, true?   That is, if you don't need to display the department in the listing, then you don't need to join them here.

  SELECT case when L.ParentIssueID is null then L.IssueId
         else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
		 , L.ParentIssueID
		 , L.TicketNumberTxt
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
         , C.Category

Open in new window



Remember that your FIRST query is for the search criteria.  There you can do all the joins to be able to search for the criteria the user specifies.   The second table, you already have the list of IDs.  You only need to select the columns that you want to display on the Listing.   If the department name is not on the display, then don't join the table.

Let me know if that helps, if not, we will dig deeper
0
 

Author Comment

by:lulu50
ID: 39942452
gdemaria,

you are 100% plus correct.  I don't need the department because I am not displaying the department.

It works beautifully!!!!!

I want to thank you so much for all your help on this I could not have done it without you!!!!

I have another question, but I have to open a new question for it.

Can you please, help me again with my new question.

Thanks,
Lulu
0
 

Author Closing Comment

by:lulu50
ID: 39942457
Excellent +++++
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 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

11 Experts available now in Live!

Get 1:1 Help Now