Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SELECT Distinct and memo field in access with coldfusion

Posted on 2003-11-25
8
Medium Priority
?
528 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
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
LVL 25

Accepted Solution

by:
James Rodgers earned 200 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

670 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