Solved

Coldfusion complex query for website Home Page - MSACCESS 2007

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

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