Re: SQL Multiple Reference Tables Question - Thanks
This is the answer. Many thanks. James Brown - Original Message - From: "Cantrell, Adam" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 4:41 PM Subject: RE: SQL Multiple Reference Tables Question > This may work, you'll want to look into using cfqueryparam around your > passed variables to avoid SQL injection attacks. You will also want to play > with the WHERE contraint to fit your needs - you may want records that match > all criteria (AND) or you may want records that match any of them (OR) > > Use LEFT OUTER JOIN, if you want to pull records that don't necessarily have > a reference. > > > > SELECT >tblThing.ThingKey, >tblThing.ThingName, >tblColor.ColorDescription, >tblCategory.CatDescription, >tblMaker.MakerName > FROM >tblThing LEFT JOIN tblColor ON tblThing.ColorKey = tblColor.ColorKey >LEFT JOIN tblCategory ON tblThing.CatKey = tblCategory.CatKey >LEFT JOIN tblMaker ON tblThing.MakerKey = tblMaker.MakerKey > WHERE > tblColor.ColorKey = #url.colorKey# > AND tblCategory.CatKey = #url.catKey# > AND tblMaker.MakerKey = #url.MakerKey# > > > > Adam. > > > > > -Original Message- > > From: James Brown [mailto:[EMAIL PROTECTED] > > Sent: Friday, February 28, 2003 3:28 PM > > To: CF-Talk > > Subject: SQL Multiple Reference Tables Question > > > > > > This is probably basic, since I am new at this, but I want to > > know what is > > "best." I have simplified the tables for illustration. > > > > I have one "main table" > > > > tblTHING > > ThingKey NameColorKeyCatKey > > MakerKey > > -- --- -- > >--- > > --- - > > 001 WinterSap 1 > > 2 > > 6 > > 002 HorsePlay 1 > > 4 > > 5 > > 003 HouseBarn 3 > > 1 > > 4 > > > > and three "reference tables." The purpose of each is to > > supply a lookup > > value to the tblThing, the main table. The foreign keys in > > the reference > > tables have the same field name as the corresponding fields > > in the main > > table. > > > > tblCOLOR > > ColorKey ColorDescription > > -- -- > > 1 Blue > > 2 Yellow > > 3 Purple > > 4 Red > > > > tblCATEGORY > > CatKey CatDescription > > - > >1 Animal > >2 Activity > >3 Human > >4 Dwelling > >5 Season > >6 Biological Substance > > > > > > tblMAKER > > MakerKey MakerName > > > > 1 Mary > > 2 Joe > > 3 Willy > > 4 Mike > > 5 Roman > > 6 Jameson > > 7 Amanda > > > > I want a query that produces the following result: > > > > ThingKey Name ColorCategory > > Maker > > -- --- -- > >--- > > --- - > > 001 WinterSapBlue > > Activity > > Jameson > > 002 HorsePlay Blue > > Dwelling > > Roman > > 003 HouseBarn Purple Animal > > Mike > > > > The values in the Color, Category and Maker columns have, of > > course, been > > supplied from the reference tables. > > > > Which I CAN do with the following Query: > > > > > > SELECT > >tblThing.ThingKey, > >tblThing.ThingName, > >tblColor.ColorDescription, > >tblCategory.CatDescription, > >tblMaker.MakerName > > > > FROM > >tblThing, > >tblColor, > >tblCategory, > >tblMaker > > > > WHERE > > tblColor.ColorKey = tblThing.ColorKey > > AND tblCategory.CatKey = tblThing.Ca
Re: SQL Multiple Reference Tables Question
Doesn't work, get the message: "the correlation name NATURAL is specified multiple times in a FROM clause" What I don't understand is - if the tables are structured in SQL Server as being related by those fields, why SQL server doesn't just "know" what the relationships are without my having to specify it. Much of my prior experience was with xBase and once tables were related to each other, all one had to do was specify the field name in the child table and the correct related record was selected. James Brown > > > SELECT > tblThing.ThingKey, > tblThing.ThingName, > tblColor.ColorDescription, > tblCategory.CatDescription, > tblMaker.MakerName > FROM > tblThing >NATURAL JOIN tblColor > NATURAL JOIN tblCategory > NATURAL JOIN tblMaker > WHERE > table.field = value > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Multiple Reference Tables Question
This is probably basic, since I am new at this, but I want to know what is "best." I have simplified the tables for illustration. I have one "main table" tblTHING ThingKey NameColorKeyCatKey MakerKey -- --- ----- --- - 001 WinterSap 1 2 6 002 HorsePlay 1 4 5 003 HouseBarn 3 1 4 and three "reference tables." The purpose of each is to supply a lookup value to the tblThing, the main table. The foreign keys in the reference tables have the same field name as the corresponding fields in the main table. tblCOLOR ColorKey ColorDescription -- -- 1 Blue 2 Yellow 3 Purple 4 Red tblCATEGORY CatKey CatDescription - 1 Animal 2 Activity 3 Human 4 Dwelling 5 Season 6 Biological Substance tblMAKER MakerKey MakerName 1 Mary 2 Joe 3 Willy 4 Mike 5 Roman 6 Jameson 7 Amanda I want a query that produces the following result: ThingKey Name ColorCategory Maker -- --- ----- --- - 001 WinterSapBlue Activity Jameson 002 HorsePlay Blue Dwelling Roman 003 HouseBarn Purple Animal Mike The values in the Color, Category and Maker columns have, of course, been supplied from the reference tables. Which I CAN do with the following Query: SELECT tblThing.ThingKey, tblThing.ThingName, tblColor.ColorDescription, tblCategory.CatDescription, tblMaker.MakerName FROM tblThing, tblColor, tblCategory, tblMaker WHERE tblColor.ColorKey = tblThing.ColorKey AND tblCategory.CatKey = tblThing.CatKey AND tblMaker.MakerKey = tblThing.MakerKey = It seems like there should be a better way to structure the query, particularly if I want to supply a variable that will display ONLY the records where the maker, category, and Color (one, all or none) meet certain conditions. (i.e. list all the Red things made by Amanda) I have tried using a JOIN, which works very well if I Join only one reference table, but does not seem to work for more than one. Is there a way to relate the reference tables in the SELECT part of the query? Thanks for any help James Brown ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Finding an Odd Number
Use the MOD operator An even Number will return a 0, and odd number will return a 1 Jason Larson wrote: > > Can someone help me out? > > I have a cfloop that loops 6 times, I want to be able to be able to output > what loop number the current loop is on, and tell if the number is even or > odd. > > Any help would be greatly appreciated ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: html ideas on this - fixed -
I'd suggest taking a close look at both grammer and spelling. There are a few errors, but I can't tell if they are just typos. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Very OT: Read if you're bored, it's not that important (was Re: OT BUT CRITICALLY IMPORTANT...)
I, for one, think this is a very important topic that should be of interest of any IT worker in the US. There are a number of academics, economists, etc. who have studied the so-called "IT worker shortage" conclude that it is basiclly bogus. Why else would Microsoft, Cisco, etc. by their own statistics, hire less than 5% of people applying for IT positions? Only about 20% of resumes for applicants for programming and other IT positions even get reviewed. If the applicant is over 35, or doesn't have exactly the right combination of buzzwords on his resume, it won't even get past automated screening. It is precisely the most experienced workers that these companies do not want to hire. Programmers over age 40 who lose their jobs average 1 1/2 years on unemployment before finding another position. Industry lobbiests fought like hell to supress a requirement that the IT Worker shortage be verified by the GAO (General Accounting Office) before taking effect. Here is a link to an editorial in the Washington Post that may open some eyes: http://www.washingtonpost.com/wp-dyn/articles/A52767-2000Sep11.html Shortage My Ass! The H1b bill is basically an industry sponsored measure to repress IT salaries and fatten the campaign coffers of your congressmen. JJB Billy Cravens wrote: > It's not a "decimation"; it's simply filling positions that > are unfilled, and unfillable by the "native" talent in our country. ... > I'll admit, there may be some merit to the argument that prices may be > driven down. Not necessarily, and certainly not $30,000 a year. ... >...It's inevitable. The only reason salaries > are so high now is that there's not enough people to go around, And because most companies will absolutely not hire anyone over 40, eliminating a huge percentage of qualified workers. > Once the industry meets demand, then the market will correct itself. An H1B visa circumvents real market forces by preventing workers from competitively shopping their skills to the highest offer, regardless of how skillful they are. > Afraid of losing money? Stay good enough that you don't have to. My advice, Don't get any older! -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Raiser's Edge
About ten years ago I wrote a fund raising (they call it "development" package) in clipper that was used by a private school and for an art's organization. During that time we evaluated Raiser's edge, and decided not to go with it because it used proprietary data structures which were not accessable outside the program. Maybe they have changed it since then. I do know that they have lots of installs, they are very big in the private school market including colleges and with many other organizations involved in fund raising. Blackbaud also has accounting and student data (grades, etc) software that they sell into the same market. There are a lot more of those than you would guess. you could have a real interesting niche market if you develop internet applications that piggy-back on their stuff. James Brown "Park, Simon" wrote: > > Has anyone ever connected CF to or even heard of Raiser's Edge, a database > application geared toward non-profit fundraising? It is made by Blackbaud > (http://www.blackbaud.com). The white paper on the application states that > it is ODBC compliant and can come with SQL Anywhere, Oracle, or SQL Server. > > If anyone has any experience, please contact me off-list. Thanks. > > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Simon Park Computer Systems Management, Inc. > [EMAIL PROTECTED] 205 South Whiting Street > Ph: 703-823-4300 x119 Suite 201 > fax: 703-823-4301 Alexandria, VA 22304 > http://www.csmi.com > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit >http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a >message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Export to Access
So where are the questions? Miriam Hirschman wrote: > > Hi, > > Thanks for helping me out. I hop you don't mind that I am emailing you > directly. I have 3 questions. Firstly, the file in excel leaves a blank > line between each record. Also before the first field there is a square > character. My last question is that one of the fields that I am exporting > to access is like a memo field and whenever I export it (using the code you > gave me or someone elses) it splits the memo field into a few records. I > see that this does happen by a period, although not all the time. > > Thanks a lot for your help, > > Miriam Hirschman > > -Original Message- > From: Eron Cohen [mailto:[EMAIL PROTECTED]] > Sent: Thursday, July 13, 2000 4:41 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: RE: Export to Access > > Miriam, > > Excel/Lotus 123 is easy. Here's a very simple solution that works great for > creating CSV files and opening them up into Excel or Lotus (or whatever is > handling comma seperated files on the client machine). The gist of this is > that CFCONTENT will cause the browser to put up an "Open File or Save" > dialog box. If they push OPEN, Excel will open up and display the info that > CF outputs: > > > > > Select * > from something > > > > > > Report For Someone: > > > "#first_name#","#last_name#","#COMPANY_name#","#state#","#country# > ","#company_address1#","#company_city#","#state#","#company_postal_code#","# > contact_phone_number#","#contact_email_address#" > > > > There are also custom tags that you can get in Allaire's tag gallery that > are more specifically tied to Excel, but excel would need to be installed on > the server. > > HTH, > > Eron > > -Original Message- > From: Miriam Hirschman [mailto:[EMAIL PROTECTED]] > Sent: Thursday, July 13, 2000 1:10 PM > To: CF Forum > Subject: Export to Access > > A client of mine wants to know if there is a way that I can export a CF page > to access or excel?? I would appreciate any info > > Thanks, > > ---miriam > > > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=sts&body=sts/cf_talk or send > a message to [EMAIL PROTECTED] with 'unsubscribe' in the > body. > > > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit >http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a >message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Developer Conference: Hotel Accommodations
Several people who went to the e-commerce meeting in Rockville a couple of months ago used priceline and got some pretty good deals on both flight and room ($80 a nite for the Rockville Marriot). For me priceline is just a little too much like gambling. I stayed in Gaithersburg at a Sleep Inn which was abour $50 a nite, and each room had a pc with high speed internet access. It was within walking distance of the Metro. James Brown Godfather of Cod, Err I mean Sole. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Excel Import
One rule to remember when designing databases: a "numeric" values that is used only as an identifier, (no math operations will be performed with it) should NEVER be a numeric type field or variable. I run into this all the time with Medical Record Numbers where the position of the digit has significance, and leading zeros are common. JJB Kelly Matthews wrote: > > to get leading zeros the data type can NOT be integer I had the same problem > had > to change the data type in the database to text in order for the zeros to > remain. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
