Help with Query

[bfabiano]

ok, I have a basic form with 3 fields for the user to fill in. Month, Week,
and office.
Now sometimes we just want the week, so we specify which week, and want all
the resultscontaining that week. Other times we may want a week, and a specific
office, etc..

Im sure this is something fairly simple, but I can figure it out.
Below is my sql statement
SELECT *
FROM ACCOUNTS
WHERE WEEK = '#URL.WEEK#' AND
INSTALLDATE LIKE '#URL.MONTH#%'
AND OFFICE = '#URL.OFFICE#'
AND INSTALLDATE LIKE '%2005'
ORDER BY CLOSER ASC

This is definetely not working. I am also using the Page Parameters with
url.week, url.office, url.month all with $ for the default. I am assuimg that
this is where the problem lies, as if I dont send a url parameter, such as a
week, I dont want it to query anything based on the week. but the url parameter
is still sent.
Please help.

Brian Fabiano

[Paul Whitham TMM]

You have installdate twice in the query which is probably a mistake, but
maybe not. The second issue is that $ is not a default value in SQL but
rather the % is. If you are using CF then I would code it like this.

SELECT *
FROM ACCOUNTS
WHERE INSTALLYEAR LIKE '%2005'
<cfif isdefined("URL.WEEK")>
and WEEK = '#URL.WEEK#'
</cfif>
<cfif isdefined("URL.Month")>
AND INSTALLDATE LIKE '#URL.MONTH#%'
</cfif>
<cfif isdefined("URL.OFFICE")>
AND OFFICE = '#URL.OFFICE#'
</cfif>
ORDER BY CLOSER ASC


--
Regards

Paul Whitham
Macromedia Certified Professional for Dreamweaver MX2004
Valleybiz Internet Design
[url]www.valleybiz.net[/url]

Team Macromedia Volunteer for Ultradev/Dreamweaver MX
[url]www.macromedia.com/support/forums/team_macromedia[/url]

"bfabiano" <webforumsuser@macromedia.com> wrote in message
news:d724a7$qmj$1@forums.macromedia.com...

> ok, I have a basic form with 3 fields for the user to fill in. Month,

Week,

> and office.
> Now sometimes we just want the week, so we specify which week, and want

all

> the resultscontaining that week. Other times we may want a week, and a

specific

> office, etc..
>
> Im sure this is something fairly simple, but I can figure it out.
> Below is my sql statement
> SELECT *
> FROM ACCOUNTS
> WHERE WEEK = '#URL.WEEK#' AND
> INSTALLDATE LIKE '#URL.MONTH#%'
> AND OFFICE = '#URL.OFFICE#'
> AND INSTALLDATE LIKE '%2005'
> ORDER BY CLOSER ASC
>
> This is definetely not working. I am also using the Page Parameters with
> url.week, url.office, url.month all with $ for the default. I am assuimg

that

> this is where the problem lies, as if I dont send a url parameter, such as

a

> week, I dont want it to query anything based on the week. but the url

parameter

> is still sent.
> Please help.
>
> Brian Fabiano
>

[Shaun Perry]

Hi

I have a query which pulls out a list of sections for my site. I would like
the query to return the user who created the section which is stored in
another table. Normally I would join the tables and this would be fine, but
my problem lies with the fact that the user table is stored in another
database. Is there any way to join the two queries?


<cfparam name="sort" default="0">

<!--- get all users available --->
<cfquery name="getContent" datasource="#SESSION.clientdatasource#">
SELECT s.*
FROM tbl_sections as s
WHERE s.siteID = #SESSION.siteID#
ORDER BY

<!--- sort order by --->
<cfswitch expression="#sort#">

<cfcase value="1">s.sectionid desc</cfcase>
<cfcase value="2">s.sectionid asc</cfcase>

</cfswitch>

</cfquery>


Any help is appreciated, thanks in advance.

Shaun

[Neculai Macarie]

> Hi

>
> I have a query which pulls out a list of sections for my site. I would

like

> the query to return the user who created the section which is stored in
> another table. Normally I would join the tables and this would be fine,

but

> my problem lies with the fact that the user table is stored in another
> database. Is there any way to join the two queries?
>
>
> <cfparam name="sort" default="0">
>
> <!--- get all users available --->
> <cfquery name="getContent" datasource="#SESSION.clientdatasource#">
> SELECT s.*
> FROM tbl_sections as s
> WHERE s.siteID = #SESSION.siteID#
> ORDER BY
>
> <!--- sort order by --->
> <cfswitch expression="#sort#">
>
> <cfcase value="1">s.sectionid desc</cfcase>
> <cfcase value="2">s.sectionid asc</cfcase>
>
> </cfswitch>
>
> </cfquery>

If you're using MySQL you can use the database.table_name syntax to select
from tables from another database (but the same server).

--
<mack />

[Shaun Perry]

i am using mysql and your help is appreciated!

Shaun

"Neculai Macarie" <mail@null.com> wrote in message
news:dbr25h$30a$1@forums.macromedia.com...

>> Hi
>>
>> I have a query which pulls out a list of sections for my site. I would

> like

>> the query to return the user who created the section which is stored in
>> another table. Normally I would join the tables and this would be fine,

> but

>> my problem lies with the fact that the user table is stored in another
>> database. Is there any way to join the two queries?
>>
>>
>> <cfparam name="sort" default="0">
>>
>> <!--- get all users available --->
>> <cfquery name="getContent" datasource="#SESSION.clientdatasource#">
>> SELECT s.*
>> FROM tbl_sections as s
>> WHERE s.siteID = #SESSION.siteID#
>> ORDER BY
>>
>> <!--- sort order by --->
>> <cfswitch expression="#sort#">
>>
>> <cfcase value="1">s.sectionid desc</cfcase>
>> <cfcase value="2">s.sectionid asc</cfcase>
>>
>> </cfswitch>
>>
>> </cfquery>

>
> If you're using MySQL you can use the database.table_name syntax to select
> from tables from another database (but the same server).
>
> --
> <mack />
>
>

[mattw]

Or you can do a query of queries. Something like:

<cfquery datasource="datasource1" name="first_qry">
...

<cfquery datasource="datasource2" name="second_qry">
...

<cfquery dbtype="query" name="getContent">
SELECT ...
FROM first_qry, second_qry
WHERE first_qry.siteID = second_qry.siteID
</cfquery>

[thesimplerules@gmail.com]

I want to have a field where I store a list of what categories that
particular row falls under, I don't know which format to do or how I
would query it.

For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
able to query all rows belonging to 3 and get row1.

[Luuk]

<thesimplerules@gmail.com> schreef in bericht
news:1156607657.696293.49730@74g2000cwt.googlegrou ps.com...

>I want to have a field where I store a list of what categories that
> particular row falls under, I don't know which format to do or how I
> would query it.
>
> For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
> able to query all rows belonging to 3 and get row1.
>

select * from something where categorie like '%3%';

[thesimplerules@gmail.com]

Luuk wrote:

> <thesimplerules@gmail.com> schreef in bericht
> news:1156607657.696293.49730@74g2000cwt.googlegrou ps.com...

> >I want to have a field where I store a list of what categories that
> > particular row falls under, I don't know which format to do or how I
> > would query it.
> >
> > For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
> > able to query all rows belonging to 3 and get row1.
> >

>
> select * from something where categorie like '%3%';

How should I seperate them.

That would also be problematic when I enter double digits, like 13.

[Luuk]

<thesimplerules@gmail.com> schreef in bericht
news:1156613142.607332.128200@m79g2000cwm.googlegr oups.com...

>
> Luuk wrote:

>> <thesimplerules@gmail.com> schreef in bericht
>> news:1156607657.696293.49730@74g2000cwt.googlegrou ps.com...

>> >I want to have a field where I store a list of what categories that
>> > particular row falls under, I don't know which format to do or how I
>> > would query it.
>> >
>> > For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
>> > able to query all rows belonging to 3 and get row1.
>> >

>>
>> select * from something where categorie like '%3%';

>
> How should I seperate them.
>
> That would also be problematic when I enter double digits, like 13.
>

I think you should store the categories of this 'row' in a different table

or, if you can not do that, you can seperate them like (i.e.) this
";1;3;7;"
(watch out for the leading AND trailing ";" in there....
you can search with something like:
select * from something where categorie like '%;3;%';

so, the first option (different table) is better

[Jerry Stuckle]

[email]thesimplerules@gmail.com[/email] wrote:

> I want to have a field where I store a list of what categories that
> particular row falls under, I don't know which format to do or how I
> would query it.
>
> For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
> able to query all rows belonging to 3 and get row1.
>

This is a standard many-to-many relationship. Use a separate table to
hold the relationship.

There's a good description of normalization at

[url]http://www.databasedev.co.uk/database_normalization_process.html[/url]

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Murdoc]

[email]thesimplerules@gmail.com[/email] wrote:

> I want to have a field where I store a list of what categories that
> particular row falls under, I don't know which format to do or how I
> would query it.
>
> For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
> able to query all rows belonging to 3 and get row1.

Storing a list of categories in a single field (such as this) is error-prone, and is extremely inefficient when searching. If you had to search for a category and return all rows with that category, the entire table would ened to be scanned to ensure that all the results are found. This is because of the way that indexes are used with strings.

A better solution, as has already been suggested, is to store the categories in a separate table.

Such as:

object = {object_id (PK), description}.
category = {category_id (PK), description}.
object_category = {object_id (U), category_id (U)} (Note that both columns for part of the unique key)

--