Solved

SELECT Distinct and memo field in access with coldfusion

Posted on 2003-11-25
8
524 Views
Last Modified: 2013-12-24
i have the following query

SELECT DISTINCT news.headline, news.message, news.news_date, news.fk_id, news.news_id
FROM news LEFT JOIN comment_news ON news.news_id = comment_news.fk_news_id
where (news.news_date BETWEEN {ts '2003-11-25 11:05:44'} AND {ts '2003-11-20 11:05:44'})
OR (comment_news.news_date BETWEEN {ts '2003-11-25 11:05:44'} AND  {ts '2003-11-20 11:05:44'})
order by news.news_date DESC

i am using M$ Access and coldfusion server 5
the message column in the news table is a memo field
with the distinct clause in there the memo field gets chopped off i am guessing at 255 chars
without distinct in there it works fine but i really need to display the results of the query with the distinct keyword
so if anyone knows of a way to restructure my query or a fix that would be great
0
Comment
Question by:jduawa
[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
  • 5
  • 3
8 Comments
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9819542
its part of access you cannot group on a memo field and the distinct cuts it off at 255 (max length of string in access), what exactly are you trying to get out of the database that requires a distinct memo field, all news items should be distinct to begin with or are you trying to get the one news item and all related comments?
0
 
LVL 1

Author Comment

by:jduawa
ID: 9819560
one news and all related comments
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9819682
<cfquery name="getNews">
SELECT news.headline, news.message, news.news_date, news.fk_id, news.news_id
FROM NEWS
</cfquery>

<cfloop query="getNews">
<cfquery name="getComments">
SELECT COMMENTS
FROM COMMENTS
WHERE NEWS_ID=<cfqueryparam value="#getNews.news_id#"
</cfquery>
<table>
   <tr>
      <th>#getNews.headline#</th><th>#getNews.news_date#</th>
   </tr>
   <tr>
      <td colspan="2">
         #getNews.message#
      </td>
   </tr>
<cfloop query="getComments">
   <tr>
      <td>#getComments.commentDate#</td><td>#getComments.poster#</td>
   <tr>
      <td colspan="2">#getComments.comment#</td>
   </tr>
</cfloop>
</table>
</cfloop>
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 25

Accepted Solution

by:
James Rodgers earned 50 total points
ID: 9819699
you will need to filter/order your queries to meet your needs and change ths comment variables to what you select from the database but the above will work for any number of news stories (1-n) with any number of associated comments(0-n)
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9819786
did you try it out?

is it working ok?


and thanks for the points!
0
 
LVL 1

Author Comment

by:jduawa
ID: 9819801
i will try it out, not sure it is exactly what i am looking for but it may work with some tweeking
the way the page is currently is that it shows the news frm the last 5 days by default then at the bottom of the news it shows how many comments are associated with that news message
when the user click on the view comments link then it goes to another page displaying the comments
if the news is older than 5 days but the comments are within the 5 day range then the message appears.

thanks for the effort
i may just move the DB to mySQL
access is crap anyway
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9819835
>>access is crap anyway

limited multi user, high overhead, only really good for local stuff

>>i may just move the DB to mySQL
good idea, not all sql is fully supported but handles things better than acces in a multi user environment

>>not sure it is exactly what i am looking for
??
it will work, just adjust teh queries to filter based on dates/newsids/ etc as yuo had in your sample code

i use similar coding in several pages, returning hundreds of records in the main query never had a problem
0
 
LVL 1

Author Comment

by:jduawa
ID: 9819853
thanks
0

Featured Post

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

628 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