display output from search results from three tables -- grouping problem

ColdFusion 9
MS SQL Server 2012

I'm having another problem with grouping. I have been working on this a while -- time to ask for help.

Here is the code:
<cfif IsDefined("URL.search")>

<cfquery name="search" datasource="#application.datasource#">
SELECT PageID
,PageTitle
,PageContentLeft
,PageContentRight
,keywords
,safeURL
,newsID
,newsTitle
,newsContent
,newsAuthor
,projectID
,ProjectName
,OSMVISTA
,City
,State
,County
,CongressionalDistrict
,Bureau
FROM #REQUEST.contentTable#, #REQUEST.NewsTable#, #REQUEST.ProjectsTable#
WHERE 
   PageTitle         LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR PageContentLeft   LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR PageContentRight  LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR keywords          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR newsTitle          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR newsContent          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR newsAuthor          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR ProjectName          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR OSMVISTA          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR City          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR State          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR County          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR CongressionalDistrict          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR Bureau          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
</cfquery>

<cfif search.RecordCount GT 0> 

<p>You searched on keyword: <strong><cfoutput>#form.searchKeyword#</cfoutput></strong>. Your result is found in these pages:</p>

<cfoutput query="search" group="PageID">

<p><strong>Web Page Results:</strong></p>
<p><a href="http://#REQUEST.companyURL#/pages/#search.safeURL#.cfm">#search.PageTitle#</a></p>

<p><strong>News Item Results:</strong></p>
<p><a href="http://#REQUEST.companyURL#/showNewsItem.cfm?newsID=#search.newsID#">#search.newsTitle#</a></p>
 
 
<p><strong>Project Page Results:</strong></p>
<p><a href="http://#REQUEST.companyURL#/showProjects.cfm?State=#search.State#">#search.State#</a></p>

<p><strong>Bureau Results:</strong></p>
<p><a href="http://#REQUEST.companyURL#/showProjects.cfm?State=#search.State#">#search.Bureau#</a></p>

</cfoutput>

<cfelse>
<p><strong>No items matched your query.</strong></p>
<p class="align-center"><a href="/index.cfm">Return to <cfoutput>#REQUEST.companyName#</cfoutput> Home</a></p>
<p>&nbsp;</p>

</cfif>

</cfif>

Open in new window



The output I get is not grouped in any useful way. I would like to group the output neatly under headers:

Web Page Results:
News Item Results:
Project Page Results:
Bureau Results:

How can I do that? =)

Thanks as always for your help.

Eric
LVL 3
Eric BourlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
HI ERic - it looks like you are trying to group by PageID, in order for the group attribute to work correctly, the sELECT statement needs to be ordered by that column (PAgeid)   so Add an order by PAgeID so your group will work.
0
Eric BourlandAuthor Commented:
Hi, gdemaria,

Good to hear from you.

It would be easy to group by PageID. However, not all search results will include a PageID.

Search results include:

Web pages, such as: http://www.doivista.org/pages/About-Us.cfm

Project pages, such as: http://www.doivista.org/showProjectRecord.cfm?ProjectID=83

News pages, such as: http://www.doivista.org/showNewsItem.cfm?newsID=110

So I need to group by PageID, ProjectID, and NewsID.

Is that possible, and am I thinking about this correctly?

Thanks as always.

Eric
0
gdemariaCommented:
WHen you say you want to group by allt hree values, do you mean in a heirarchy?

  Web pages...
  -  PRoject Pages
  -  - News PAges

Or do you mean on or the other or how would it look?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Eric BourlandAuthor Commented:
Not in a hierarchy.

Here's what I had in mind:

* user enters search keyword
* ColdFusion displays database records that contain that keyword
* records can be web page (PageID), news story (newsID), or project page (projectID)
0
_agx_Commented:
>> FROM #REQUEST.contentTable#, #REQUEST.NewsTable#, #REQUEST.ProjectsTable#


Hi Eric - This may only be tangentially related to your question, but it looks like there's no JOIN between any of those tables. So you're going to end up with a cartesian product ie a LOT of records.  That's almost certainly NOT what you want.

What's the relationship between those 3 tables?
0
Eric BourlandAuthor Commented:
_agx_

Yep -- you're right -- no JOINs.

There's no relationship among the tables. Maybe this was not the best way to build these tables. There are separate tables for

web pages
news
projects

and there is not any link among them such as a consistent record ID.

At this point I can't really rebuild the three tables.

But I welcome ideas. =) Thank you as always.

Eric
0
gdemariaCommented:
No need to rebuild the tablesif  the data is not related, but you do want to seperate them into three different queries.  That will make your search results a lot easier too.   doing this will give you three seperate sections for your results news, web pages, projects...   each driven by its own query.  

IF you want the results mixed together, you can UNION the select statements together
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
To be honest, the simplest option may be to run separate queries for each table.  Then output each one.

However, if you're only using a few columns on the search screen - say "ID", "Title" and "Content"  - and all 3 tables have those columns (or something similar), you could use a UNION to  merge the results from the disparate tables together into one query.  It would be bulky though.

For example


SELECT WebPageID AS ID, WebPageTitle AS Title, WebContent AS Content FROM FirstTable WHERE .....
UNION ALL
SELECT NewID AS ID, NewsTitle AS Title, NewContent AS Content FROM Second WHERE .....
UNION ALL
.... etc....
0
_agx_Commented:
Took to long writing as usual.  Never mind ... what gd said ;-)
0
gdemariaCommented:
we said the same thing, but agx said it better; we're on the same page.    If you want your results in three sections; use three separate queries and loops.  If you want one results with the types mixed in, use UNION.  It seems from your question about grouping that you want the first option.
0
Eric BourlandAuthor Commented:
>>>To be honest, the simplest option may be to run separate queries for each table.  Then output each one.

I think this makes the most sense.

I am going to try this now; I'll come back here and let you know what I get.

Thank you as always. =)
0
Eric BourlandAuthor Commented:
This is coming together with the three separate queries.

I'll follow up on this later tonight or tomorrow.

Thanks so much gdemaria and _agx_.  Hope your respective days are going well. We just had a torrential storm here in DC. I'm off to survey the yard and house. =)

Eric
0
Eric BourlandAuthor Commented:
gdemaria and _agx_,

This is working very well and the client is satisfied.

Thanks as always. Hope you both have a great evening. Take care.

Eric
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

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.