Skip to main content
B
blackandwhite
Inspiring
July 30, 2009
Question

Select from multiple entries in table cell.

  • July 30, 2009
  • 2 replies
  • 1091 views

Hi All,

I'm having issues when trying to do a simple select from an access database:

<CFQUERY NAME="GetAllByMonth" DATASOURCE="dsn">

SELECT * FROM table WHERE  strMonth = '#url.strMonth#' AND strDisplay = Yes
order by strOrder_id

</CFQUERY>

Table cells in rows contain coma separated values:

January, February, March, April, May, June, October, November, December

Unfortunately the query returns no values. I've tried changing the "=" with "like", "contains" but with no results.

Difficult to know what to search for so any help gratefully recieved.

Ta

Trevor

This topic has been closed for replies.

2 replies

T
TLC-IT
Inspiring
August 13, 2009

Here is an absolutely crazy "hack" that I once used when dealing with a database like that.  It had hundreds of thousands of rows with comma separated values that for various legacy could not be changed.

So one day I did this ...  SELECT DISTINCT column_from_purgatory FROM table_from_hell.

Much to my surprise, there were less than a hundred DISTINCT values in that column.  (They were, fortunately, reasonably-sized VARCHARs.)

So I built a table of those values, then created a separate table into which I could put each of the comma-separated parts, and then wrote a small (Perl) script to populate the two.  For example, a row containing "January, March, April" would be linked to three rows containing one month-name each.

Now, as long as I had a way to be sure that this pair of lookup-tables was always complete (which was slightly problematic), I had something that I could actually JOIN to.  It wasn't pretty ... in fact, it was butt-ugly ... but it saved a tremendous amount of processing time for this application.

D
Dan_Bracuk
Inspiring
July 30, 2009

Normalize your database and you can retrieve your data with simple queries.  If you don't understand that sentence, I've heard good things about the book, Database Design for Mere Mortals.

B
blackandwhiteAuthor
Inspiring
July 30, 2009

Hi Dan,

Many thanks for your advice. Off to do some reading.

Cheers

Trevor

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices