Solved

Coldfusion complex query for website Home Page - MSACCESS 2007

Posted on 2011-02-15
5
948 Views
Last Modified: 2012-05-11

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

0
Comment
Question by:onlyamir007
5 Comments
 
LVL 9

Assisted Solution

by:Shaun McNicholas
Shaun McNicholas earned 167 total points
ID: 34906221
SELECT TOP 4 *
FROM Tbl_Content, Tbl_category
WHERE Tbl_Content.contid  = Tbl_category.catid
ORDER BY RAND()

TOP 4 selects only 4 records -
* means it will return all columns from both tables
ORDER BY RAND() will put them in random order and then return only the 4 records you asked for

If you want to select them from a specific category then you just put another statement in the WHERE clause like
SELECT TOP 4 *
FROM Tbl_Content, Tbl_category
WHERE Tbl_Content.contid  = Tbl_category.catid
             AND Tbl_category.catName = 'Category to Select'
ORDER BY RAND()
 
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 167 total points
ID: 34907456

I believe you need to use Rnd() for MS Access.
I'm using the primary key as the seed...

SELECT TOP 4 ca.catName, co.title, co.contID
FROM Tbl_Content  co
  left join Tbl_category ca on ca.catID = co.catID
ORDER BY Rnd(co.contID)

But this will not guaranteed you one record per category (neither will the previous post), both could give you duplicate categories.
 
0
 
LVL 11

Assisted Solution

by:Brijesh Chauhan
Brijesh Chauhan earned 166 total points
ID: 34913484

>> But this will not guaranteed you one record per category (neither will the previous post), both could give you duplicate categories.

After running the above query, you can use QoQ in CF to get distinct categories

From gdemaria query above

<cfquery name="Random" datasource="#dsn#">
	SELECT TOP 4 ca.catName, co.title, co.contID
	FROM Tbl_Content  co
  	left join Tbl_category ca on ca.catID = co.catID
	ORDER BY Rnd(co.contID)
</cfquery>

Open in new window


Then use dbType are query to filter duplicate categories

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

Open in new window

0
 
LVL 4

Author Closing Comment

by:onlyamir007
ID: 34914079
Thanks i did something my own but really thanks for help
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34916179


<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
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

778 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