Solved

Coldfusion complex query for website Home Page - MSACCESS 2007

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now