Because there are a number of ways of relating the information when dealing with it - having that flexibility is necessary. If you want cleaner CF code, create stored procedures or views that will offer exactly what you need.
The reason you specify the relationships in the database are for a number of reasons, a couple are: - Enforcement of referential integrity. "When I delete this color from the color table, I also want to make sure all references to that color ID are deleted". When you don't enforce referential integrity and you just leave empty references all over the place, you'll end up with what they call Delete/Update Anomalies - mess your application up big time. - Speed - the pri/foreign keys play a heavy role in the RDBMS's indexing and internal performance optimization. I'm sure there's more that are currently escaping me. Pick up Celko's 'SQL for Smarties' - improve your SQL by 100%. Adam. > -----Original Message----- > From: James Brown [mailto:[EMAIL PROTECTED] > Sent: Friday, February 28, 2003 4:15 PM > To: CF-Talk > Subject: 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 > > > > > > <CFQUERY name="thinglist" datasource="mydata"> > > 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 > > </CFQUERY> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
