Solved

Coldfusion complex query for website Home Page - MSACCESS 2007

Posted on 2011-02-15
5
956 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
[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 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 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