Link to home
Start Free TrialLog in
Avatar of onlyamir007
onlyamir007

asked on

Coldfusion complex query for website Home Page - MSACCESS 2007


Dear Experts,

Im writing query for website home page I want to show current month and distinct  category records on home page for example
One category is “kids park”  one category is “ Shopping Malls” all categories has more then 200 records   I want to show one record  from each category it should be random  record from current month,  each category will have approximately 4 records in each months

Please find blow my Database tables  im trying to write this complex code in clean way in don’t want dirty code that’s why im asking you guys,

TABLE NAME  -  Tbl_Content
1.1.      contid
1.2.      catid
1.3.      title
1.4.      description
1.5.      hpImage
1.6.      inImage
1.7.      CreationDate
1.8.      ExpiryDate
1.9.      hpOrderNum
1.10.      Archive
1.11.      ApprovalStatus

TABLE NAME  -       Tbl_category
2.1.      catid
2.2.      catName
2.3.      parented


Relation Ship
Tbl_Content.contid  = Tbl_category.catid

Cold Fusion 8
Databse MS Access 2007

SOLUTION
Avatar of Shaun McNicholas
Shaun McNicholas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of onlyamir007
onlyamir007

ASKER

Thanks i did something my own but really thanks for help


<cfquery name="display" dbtype="query">
      select distinct(catName), title, contID
      from Random
</cfquery>


This won't work.  

This will give you a list of disintct catName, title and contID, which will show the same results as the Random query.  

The objective was to have each category name appear Once with one random title.

it would be closer to do...

  select catName, max(title)
  from random
   group by catName

but that would not give a random title.

onlyami007, why don't you post your solution for the EE archive