Solved

SELECT Distinct and memo field in access with coldfusion

Posted on 2003-11-25
8
521 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
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 exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …

838 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