SQL: Outer Join w/ criteria
Howdy! Here's the situation: I've got a table of events, Events, and a table of categories, Cats. Each event can be in several categories so I have a third table (EventsXCats) joining Events and Cats. I want to create a form to admin this system and would like to dynamically present the categories. I can handle the presentation and processing of the fields but I'm having difficulty with displaying existing data for edit. What I want to do is an Outer Join so I get all of the categories and additional data if the event in question is in a particular category. So I tried this: SELECT a.CatName, b.EventID FROM Cats a LEFT OUTER JOIN EventsXCats b ON a.CatID = b.CatID WHERE b.EventID = 123 What I expected: EventName EventID -- --- Concert 123 Exhibition 123 Seminar null What I got: EventName EventID -- --- Concert 123 Exhibition 123 Can you combine an Outer Join with WHERE criteria? I guess I thought that was possible. If its not possible, how have others resolved this same problem? Kevin -- Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Determine the number of form fields...
Peter, You can access a list of all submitted fields in the Form.FieldNames variable. Combine that with strategic naming conventions for your dynamically created form fields and you should be good. For example, I was working on a form today that has dynamic checkboxes based on records in a db. I named each box "Standard#var#". Now I can check the Form.FieldNames list with ListContains() for the list element position of fields with the "Standard" prefix and get the field value with Evaluate(ListGetAt(the position)). hth, Kevin At 05:17 PM 8/16/00 -0400, you wrote: Hello, I have a form, which has an uncertain number of fields. These fields are produced dependant on data in the database, the more data, the more fields. My question is, I need to be able to perform an update/insert into the database, but I'll never know for sure what/how many fields exist to perform this query. Has anyone done something similar to this, what would the best method to employ? Thanks, Peter ------ Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: SQL Question (sorta OT)
John, You should be able to say: AND NOT omit = 'yes' That's assuming that omit is a text field and not a boolean (yes/no) field. If its boolean you could just say: AND omit = false hth, Kevin At 01:33 PM 8/11/00 -0700, you wrote: Hi all Here the query I'm trying to edit: CFQUERY NAME="LookUpResults" DATASOURCE="WebData" SELECT CompanyName, City, State, CompanyURI FROM Main (Index=ix_Main_CompanyURI) WHERE CompanyURI IN (#QuotedValueList(CompanyNameSearch.Custom1)#) ORDER BY CompanyName /CFQUERY I would like to add "omit 'yes' " to the WHERE clause. When I do this, the query returns 0 records (unmodified, it returns 25 records). Any ideas are greatly appreciated. * John * ------ Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: QUERY QUESTION
Kelly,
All I can do is guess since I don't know your db design but here goes:
-Run your first query
-Run this query
cfquery name="qgetitems" datasource="#DSN#"
SELECT COUNT(SI_Item_ID) AS SI_Item_ID_COUNT, SI_Item_ID
FROM SI_ITEMS2
WHERE SI_ITEM_ID IN (#SI_Item_ID#)
AND SI_Opsrpt_ID = #ValueList(qgetsi.SI_Opsrpt_ID)#
GROUP BY SI_Item_ID
/cfquery
-output like this
CFOUTPUT query="qgetitems"
#SI_Item_ID_COUNT# - #SI_Item_ID#
/cfoutput
I would also suggest that you not do SELECT * but specify the fields you
want instead. It may take a little more work but will make debugging
and/or interpreting your code much easier.
hth,
Kevin
At 02:04 PM 8/10/00 -0400, you wrote:
Ok here is my perdicament. Yours worked fine
cfquery name="qgetitems" datasource="#DSN#"
SELECT *
FROM SI_ITEMS2
WHERE SI_ITEM_ID IN (#SI_Item_ID#)
AND SI_Opsrpt_ID = #qgetsi.SI_Opsrpt_ID#
/cfquery
however what I am really trying to do is get a count.
So say there are 3 SI_OPSRPT_ID's
and i pass 5 item_ids i want to do a count of how many times
each ITEM shows up within those 3 reports.
I'm still somewhat new at getting things to query and display properly.
would want the output to be something like.
From this date to this date (i pass the date range thats what determines how
many SI_opsrpt_id's will be included):
There were 7 instances of this Item.
???
Kelly
---
Kelly Matthews
Internet Development Coordinator
AAAE
703.578.2509
[EMAIL PROTECTED]
http://www.airportnet.org
---
snip
-Original Message-
From: Kelly Matthews [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 10, 2000 12:14 PM
To: '[EMAIL PROTECTED]'
Subject: QUERY QUESTION
Ok I have 2 queries I am running the first is:
CFQUERY datasource="#DSN#" name="qgetsi"
SELECT *
FROM SelfInspection
WHERE 0=0
CFIF (IsDefined("form.date1")) AND (IsDefined("form.Date2"))
AND SI_dt BETWEEN #CREATEODBCDate(form.date1)# AND
#CREATEODBCDate(form.date2)#
/CFIF
/CFQUERY
This runs fine...
Next one is:
cfquery name="qgetitems" datasource="#DSN#"
cfloop list="#SI_Item_ID#" index="TheItem"
SELECT *
FROM SI_ITEMS2
WHERE SI_ITEM_ID = #TheITem#
AND SI_Opsrpt_ID = #qgetsi.SI_Opsrpt_ID#
/cfloop
/cfquery
This seems to run fine but comes back with ONE record when it should come
back with THREE:
Results:
qgetitems (Records=1, Time=0ms)
SQL = SELECT * FROM SI_ITEMS2
WHERE SI_ITEM_ID = 19 AND
SI_Opsrpt_ID = 5
SELECT * FROM SI_ITEMS2
WHERE SI_ITEM_ID = 13
AND SI_Opsrpt_ID = 5
SELECT * FROM
SI_ITEMS2
WHERE SI_ITEM_ID = 15
AND SI_Opsrpt_ID = 5
It only appears to bring back ONE record however it should bring back 3
because
ID 5 is in there with item id 19,15 and 13 but it seems to only pick up
and
display the first one
Do I need to change my query
Kelly
--
Kevin Marshall
Web Application Developer
eCalton.com, Inc.
Vero Beach, FL
www.ecalton.com
[EMAIL PROTECTED]
561.569.4500
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: output query as array
Eli, You can refer to records in an query result using array syntax like this: arrayname.fieldname[recordnumber] For example: cfquery name="testing123" datasource="foo" SELECT name, rank, serialnumber FROM peoples /cfquery cfoutput #testing123.name[2]# #testing123.rank[2]# #testing123.serialnumber[2]# /cfoutput That presumes that you know the record number that you want - or that you are otherwise sure that there _is_ a record 2. Hope that's what you were looking for... Kevin At 03:51 PM 8/3/00 -0400, you wrote: Hi i was told that a query result set is like a array. how do i output it with the rows and columns this will be placed in a table but the rows and columns are both dynamically created so i cant make static headers on the table. if i cant just output the whole thing then i would like to make a array and then output that as a whole. Any Ideas??? -Eli- ------ Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Need to take several row query and make into one row.
Rick, You may want to take a look at a custom tag called CF_QueryToStruct. Basically it will take a query like yours and convert it to a struct that would look like this: MyQuery.MyVar.MyValue I've used this tag with great success in a situation something like yours. Find the tag at: http://devex.allaire.com/developer/gallery/info.cfm?ID=CA34740D-2830-11D4-AA 9700508B94F380method=Full (yep, the url probably wrapped - if you want, just search in the Developers Exchange for "QuerytoStruct") Kevin At 05:16 PM 7/9/00 -0700, you wrote: Hello, The "why" for the following is an email unto itself, so if possible any ideas on "how" would be greatly appreciate. The cfquery name="MyQuery" is selecting from the following db table: MyKey|MyVar| MyValue - 0001|ClientName |Rick 0002|SeminarsFor|aerospace 0003|RAMPrice|stable Now I need to write (without looping or cfifing or cfswitching) or (changing the the table itself) and (caching of some sort is desired): The name of your seminar is: #MyQuery.SeminarsFor# The state of CA is: #SomeOtherQuery.HighTaxes# The only way I've been able to do it so far is to rebuild the above table into: ClientName |SeminarsFor|RAMPrice Rick|aeospace| stable But this constant table manipulation isn't practical. Is the answer in sql? structures? arrays? or ? TIA, Rick -- Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: lists and variable names
Adrian, I would really use either a Structure or Array for this - its really the ideal application for these more complex data types. Here's how you could use a Structure: cfset Cities = StructNew() cfset Cities = StructInsert(Cities, "Atlanta", AtlantaList) cfset Cities = StructInsert(Cities, "Dallas", DallasList) A specific city's list is available by referencing: Cities["Atlanta"] or Cities["Dallas"] cfoutput Atlanta - #Cities["Atlanta"]#br Dallas - #Cities["Dallas"]#br /cfoutput CFLOOP COLLECTION=#Cities# ITEM="TheCity" !--- This loop will loop over all of the city names you have set up as structure keys; within this loop each city specific list is available by referencing: Cities[TheCity] --- cfoutput #TheCity# - #Cities[TheCity]#br /cfoutput /cfloop I didn't test this - so buyer beware! Kevin At 09:49 AM 7/7/00 -0700, you wrote: Im having a problem linking a variable name to a list name, and cant figure out a way around this. Basically I have a List of Cities and each City in the Cities List has its own List. I have 2 loops, the outer loop processes one City at a time and the inner loop process's the list for the current city. This is the code Im basically using (trimmed down), the Cities and Cities Names Lists are dynamic and will vary based on other queries: cfset Cities = "Atlanta,Dallas" cfset Atlanta = "AB1,AB2,AB3,AB4" cfset Dallas = "DB1,DB2,DB3,DB4,DB5,DB6,DB7" cfset Loop1Count = 1 cfloop index="i1" from="1" to="#ListLen(Cities)#" cfset SingleCity = listgetat(Cities, #Loop1Count#) cfset SingleCityList = #SingleCity#) !--- I need to somehow connect the City with it associated list. I know this does not work, SingleCityList ends up being Atlanta rather than the Atlanta List (AB1,AB2,AB3,AB4). --- cfloop index="i2" from="1" to="#ListLen(singlecitylist)#" Do some processing !--- I need to have the Atlanta List available in this loop for processing --- /cfloop cfset Loop1Count = Loop1Count +1 /cfloop There must be some way around this, Ive tried putting the lists into an Array (Im new to arrays) but a had a problem because each list was not the same length. Any recommendations on how to accomplish this...? Thanks,Adrian -- Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Looping through an Array
Gene, You can loop through 2/3D arrays like this: cfset mytest = ArrayNew(2) ...load the array... cfloop from="1" to="#arraylen(mytest)#" index="OuterLoop" cfloop from="1" to="#arraylen(mytest[Outerloop])#" index="innerloop" cfoutput#mytest[outerloop][innerloop]#/cfoutputbr /cfloop /cfloop The same holds true in 3d - just add an additional loop. Note that you must reference the particular dimension element you are in when starting a new loop e.g.: " to="#arraylen(mytest[OuterLoop])#" " Kevin At 10:14 AM 6/30/00 +, you wrote: Taking this one step further, I'd be interested in seeing an example of the best way to loop through a 3-dim array. ArrayLen only shows the length of the first dimension - how do you determine the size of the 2nd and 3rd? Gene Kraybill Is there a way that i can loop through an array and display all the items Why, certainly: cfloop index="i" from="1" to="#ArrayLen(myarray)#" cfoutputItem #i#: #myarray[i]#/cfoutputbr /cfloop Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ------ -- Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: CF logins, authenticate against Novell NDS???how?
I did a little searchin' in the Allaire forums and came up with an interesting post about this subject: http://forums1.allaire.com/DevConf/Index.cfm?Message_ID=487949 This explains how to generate the certificate for the SSL link to the Novell LDAP server. You also may want to search for "LDAP" at: http://developer.novell.com/research/index.htm If you can get through the 'novell-speak' there's some pretty good articles here on a variety of LDAP issues - although nothing specific to CF. Kevin At 10:56 AM 6/2/00 -0700, you wrote: Lanny et al..., Care to point us NDS "newbies" in the right direction via URL to stuff on integrating NDS w/ cf4.5? Any help appreciated Steve ------ Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: I want my #$# on the first of the month.
Tim,
jeez, I hope you figured this out by now but if not...
cfset thismonth = Now()
cfset nextmonth = DateAdd("m", 1, thismonth)
cfset NMyear = DatePart("", nextmonth)
cfset NMmonth = DatePart("m", nextmonth)
cfset NMday = 1
cfset finalval = CreateODBCDate(CreateDate(NMyear, NMmonth, NMday))
This may be the long way to do this - and will hopefully be
self-explanatory. I would probably do some sort of ugly
functionwithina(functionwithina(function))) kinda thing in real
life. Don't know if that's a good thing though...
hth,
Kevin
At 04:32 PM 5/31/00 -0400, you wrote:
I have been on a 23 hour coding spree to finish up a project. How do
you create an odbc date value for the first of next month? I thought
i had it but something is escaping me. I am tired and this is the
last thing.
thanks
Tim Hill
-- Timothy C. Hill, [EMAIL PROTECTED] on 05/31/2000
------
Kevin Marshall
Web Application Developer
eCalton.com, Inc.
Vero Beach, FL
www.ecalton.com
[EMAIL PROTECTED]
561.569.4500
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: OT: Sql for selecting date ranges
Shawn, Sounds like BETWEEN should work for you: SELECT foo... FROM bar... WHERE adatefield BETWEEN #firstdateval# AND #seconddateval# You'll want to be careful if your adatefield contains time data - not sure how that would affect query results - though the query will run. hth, Kevin At 03:08 PM 5/31/00 -0700, you wrote: What is the best way to go about selecting date ranges. Like selecting everything from today's date up to 5 or 6 days from now? Shawn Regan -- Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: CF logins, authenticate against Novell NDS???how?
Paul, You should be able to access NDS directory info via CFLDAP. Your network admins will need to set up an LDAP server on one of your Novell servers -- I don't think it is installed by default on a Novell server. I know others on this list have done this -- to be honest, I haven't but have a fair amount of NDS experience from a "previous life". Kevin At 10:45 AM 5/31/00 -0400, you wrote: Does any one have any info on how to use NDS to build in a login to a cf app? My maneger wants me to start working with real authenticated user info, instead of just Data that i have enterd into a mdb. i dont really know where to start. any Guidence would be wonderful! -paul -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Shortcut for Getting Form Field
Steve, I suppose you could submit your form to a 'do-nothing' processing page and then steal the list of passed form fields from the debugging info (you do have debugging turned on, right?). Be x-tra careful though with radio buttons and checkboxes since they won't be passed unless they are selected. Kevin At 01:42 PM 5/22/00 -0400, you wrote: I'm working on a project that has tons of form fields on a page. I was wondering if there was any cf tags or anything that I could take the form, submit it, and get a list of all the form field names that are available to me without having to go through and doing all the copying pasting... -- Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Slightly OT: Lighten things up a little
I know, I know, cf-community... Anyway, who's selling the WWBFD bumper stickers? I'll buy one! Kevin At 06:58 AM 5/9/00 -0500, you wrote: - Have a bumper sticker reading 'WWBFD'. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: What's the use of structures, anyway?
Reuben, I totally understand what you're saying. When I first considered structures, they seemed like really complicated mechanisms for holding data that could be just as easily placed into a query or a list. What I've found, however, is that structures can be incredibly powerful if you think of them as 'objects' which can contain all of the pertinent data for that object. Also, as Mike suggests (perhaps a little energetically), structures really take off when you create nested structures. I've been working with nested structures to manage navigation elements and it has made my job so much easier. If I need to add a new nav element, I just create a new structure that describes that elements attributes (name, link, image, etc.) and place that struct into the master navigation holding struct. I make no claims of brilliance, but the site I implemented this on has experienced quite a few changes and nav changes have been a breeze. I would encourage you to work with structures a while before you discard them as useless -- they're used pretty extensively in other languages, so the concept has proven useful before. Another note: you may want to look into using CFScript when you manipulate structs. It is TONS easier to use cfscript than the Struct* functions inside cfset tags. Good luck! Kevin At 01:48 PM 4/20/00 -0500, you wrote: Structures seem totally useless to me. If they were two dimensional, then I could see definite value. I'd love to see something like: cfset access_flags["is_superuser"].default_value="false" cfset access_flags["is_superuser"].description="Is this user an administrator?" cfset access_flags["is_superuser"].data_type="boolean" cfset access_flags["is_superuser"].user_assignment="true" cfset access_flags["access_emps"].default_value="false" cfset access_flags["access_emps"].description="Does this user have access to the employee records?" cfset access_flags["access_emps"].data_type="boolean" cfset access_flags["access_emps"].user_assignment="true" .. etc ... As I understand it right now from reading the CF docs and Forta's books, structures are nothing at all but a one dimensional shorthand way of passing around a single row of key/value pairs. Am I correct in this? I guess I'll just use the QueryNew() function and its relatives.. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: What happened to this list?
Marcus, I, for one, would like to know who sent that message to your boss. I'd hate to make the mistake of answering a question for or even bothering to read a post by such a despicable individual. I too lament the recent angry tone on the list. I've been reading CF-Talk for almost 2 years and what meager programming skills I have developed are mostly due to the advice and examples that have crossed the list. I'd encourage everyone on the list, new and old, to respect the list and its members. As in most collective efforts, sullying the environment of CF-Talk is really "sh|tting where you eat". Kevin At 02:51 PM 4/20/00 -0500, you wrote: I don't want to keep this thread going but I think that is very wrong of someone to insult others on this list, were all here trying to learn from one another not insult one another. Try this one... Recently I posted a question I had to the list and no one responded. Not a big deal, everyone has a life. Today the owner of the company I work at brought me a copy of an email he recieved from someone on this list... it basically said that the programmer he had hired was unable to accomplish a minor task like the one I mentioned, and he had, out of the goodness of his heart, searched out the company to offer his skills should my company wish to 're-think the current development staff'. My boss reminded me that I should be careful of the people I associate with, and left it at that. Aren't I lucky! Marcus Dr. Marcus Kiel Director of Development AdTel International -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Deleting Data
Tim, I think I've generated that error before. You should check your form method=... tag to make sure that it is right. Check your code and the browser source - strange things might be happening if you are referencing variables in the tag. It that all looks okey-dokey, you may want to post your code. Kevin At 04:55 PM 4/20/00 -0400, you wrote: I am trying to create a page where a user can delete their data from the DB. I have created a CFQUERY with a DELETE statement. When I try to perform the function I get a page that reads 405 Method Not Allowed. Am I missing something with my CF Server settings? Thanks Tim -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: three table sql join
Deanna, It looks like you are trying to perform a left outer join between a single table on the left and two inner joined tables on the right. If that's the case, I've tried to do this several times - also in Access - and never gotten it to work. There is a workaround, though. Whether you consider it advisable is up to you. You can create a query _in_Access_ that performs the inner join and then reference the Access query as a table. Works pretty slick and doesn't seem to negatively affect performance. The downside is that you are stuck with the Access query which you gotta remember to have in your database (if you have multiple copies...dev prod, etc.) and which you may have to change to add/remove fields in the future. Hope this helps, I'll be watching the thread for other, wiser ideas. Kevin At 11:26 AM 4/17/00 -0500, you wrote: This is slightly off topic, but I'm sure there are sql gurus out there who know how to do this. I'm trying to join three tables (category, subcat, catrelation), pulling all records from category, and using the cf_twoselectsrelated tag to output the results, so that if someone chooses a category, the second select box is populated with the appropriate subcategories. I have it working when I don't care if the categories that don't have subs are included. But I do care! So, how do I get this to work in one query that I can reference in the custom tag? Oh yah, and this is in access right now. The error I get is "join type not supported." I've already tried going back to access to create the query, and it basically tells me I have to do it in two queries Grrr. Frustrated. cfquery name="pullcats" datasource="#application.dsn#" SELECT c.cat_id, c.cat_name, s.subcat_id, s.subcat_name FROM category c left outer join catrelation r on c.cat_id = r.cat_id, subcat s WHERE c.cat_id = r.cat_id AND s.subcat_id = r.subcat_id /cfquery THanks! -d Deanna Schneider Interactive Media Developer UWEX Cooperative Extension Electronic Publishing Group 302 Hiram Smith 1545 Observatory Dr. Madison, WI 53706 (608) 265-7923 ------ Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: DB update with email notification
Karen, Is there any reason that you couldn't use the CFMail tag in the same cf page that inserts the form fields into the data? It would look like this: ---user submits form--- cfquery... (or cfinsert) Insert into... /cfquery cfmail to=#form.to# from=#form.From# cc=#form.CC# this is an automatic notification that the database has been updated! /cfmail I would recommend that you do the mail message after the insert (or update) so that if the db operation fails, the message doesn't get sent prematurely. We currently do something almost exactly like this - except that we don't always use form variables for the from,to,...etc. hth, Kevin At 10:04 AM 4/13/00 -0400, you wrote: Hi, I have a form right now that updates a database in CF. I would like for an automatic email notification to occur after the database has been updated. The "to, from and cc" fields would be included in the update form. Currently, we are using a form2email perl script for email notification. I am not familiar with CFMail. Any ideas how this could be accomplished within one form? Thanks! Karen ------ Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re:
David ahhh, your crafty ploy of posting without a subject has sparked my curiosity and forced me to read your message! ;) If you are concerned about your query not returning any records - resulting in an empty table - do something like the following: cfif results.recordcount GT 0 ---do all your table stuff here --- cfelse Sorry, your search returned no records...etc.etc. /cfif A couple extra notes - added at no extra cost to the consumer: -You don't need #'s in your CFIF's Example: cfif CeramicSeries IS "NOT CERAMIC"... -You can use NOT within a CFIF to reverse the test and avoid a CFELSE Example: cfif NOT material IS "Ceramic Tiles" #material# /cfif hth, Kevin At 04:11 PM 4/7/00 -0400, you wrote: Fellow CF scriptors -- I am using the following code to "pour" search query results into a three column layout. I can't for the life of me figure out how to deliver a "no matching items found" when there are no matching items found. I've tried IFs and SWITCHES and others, but it's no use. What I am missing? TIA, David snip David Ryan - Minister of Information and Culture CRUMBLING EMPIRE PRODUCTIONS 134 W.26th St. 12th Floor New York, NY 10001 (212) 247-0296 -- Kevin Marshall Web Application Developer eCalton.com, Inc. Vero Beach, FL www.ecalton.com [EMAIL PROTECTED] 561.569.4500 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
