• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 963
  • Last Modified:

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

0
onlyamir007
Asked:
onlyamir007
3 Solutions
 
Shaun McNicholasSenior Marketing TechnologistCommented:
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
 
gdemariaCommented:

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
 
Brijesh ChauhanStaff IT EngineerCommented:

>> 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
 
onlyamir007Author Commented:
Thanks i did something my own but really thanks for help
0
 
gdemariaCommented:


<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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now