I think what Dave was getting at was that when you perform a cfquery, CF removes the table prefixes unless you alias the column.
e.g.: <cfquery name="qryFoo" ..> select m.bar from tableFoo </cfquery> You would reference the column like #qryFoo.bar# and not #qryFoo.m.bar# If you references similar columns in SQL Server, you are right that as long as they are prefixed differently they will be returned. It just boils down to why you include duplicate copies of the foreign key relationships. Even if you are pulling back outer joins, you typically handle the nulls prior to manipulating them in CF. And the benefit for * is extremely marginal. If you cache any of your connections, I would be hard pressed to justify using * from speed. There is nothing wrong with using it, but it is not very friendly to the developers that code use it behind you. If that doesn't matter, I would not worry too much about it. Teddy On 11/17/06, Bobby Hartsfield <[EMAIL PROTECTED]> wrote: > > First, Yes, you are right. It IS faster to use * when you need every field > but in your case, you DON'T need all the fields so using * is ONLY saving > you time in typing, not processing. Like I said before, the 2 fields most > likely have the exact same value or they wouldn't be named the same in two > related tables. > > Second, you are right again... the driver isn't supposed > to make up names.... that is why it's not. It is using the names you told > it > to use when it runs into a case where 2 exact field names are returned. > > What do you expect to get from it? 1 Main_Category_ID when there are > obviously 2? > > If you run the same query in access or sql server, you are going to get > both > columns back with the same exact names. C.Main_Category_ID and > M.Main_Category_ID. I'd say that's what you get in any other database as > well but those are the only 2 I tried. > > > ..:.:.:.:.:.:.:.:.:.:.:. > Bobby Hartsfield > http://acoderslife.com > > > > > > -----Original Message----- > From: Bud [mailto:[EMAIL PROTECTED] > Sent: Friday, November 17, 2006 3:03 PM > To: CF-Talk > Subject: RE: Queries adding table Alias to column name > > The point is. This isn't expected behavior. The driver isn't supposed > to make up names. I'd alias the column myself if I wanted that. I've > never ever seen this. I see many people using just * to grab all the > fields in all the tables in a query. When you need all the fields, * > is faster than specifying all the field names. Faster to type and the > query runs faster. > > > >What's changed about it? Anyway, Why can't you just exclude it from one > of > >the tables like you said. My guess is that they are identical values > every > >time anyway. Why have it in there twice? > > > > > >..:.:.:.:.:.:.:.:.:.:.:. > >Bobby Hartsfield > >http://acoderslife.com > > > > > > > > > > > >-----Original Message----- > >From: Bud [mailto:[EMAIL PROTECTED] > >Sent: Friday, November 17, 2006 8:13 AM > >To: CF-Talk > >Subject: Queries adding table Alias to column name > > > >ARRGGHH!! Is this a CF issue or an Access database driver issue? > > > >http://azarflooringgallery.com/bud.cfm > > > >This is a very simple, and quite standard query. > > > >SELECT M.*,C.* > >FROM Main_Categories M LEFT OUTER JOIN Categories C on > >M.Main_Category_ID = C.Main_Category_ID > > > >Note the Main_Category_ID is being returned with the alias in it's > >name. The field is actually named Main_Category_ID > > > >C.Main_Category_ID and M.Main_Category_ID > > > >I'm getting the error Variable MAIN_CATEGORY_ID is undefined. > > > >I can get it to go away, but only if I manually enter all field names > >and exclude Main_Category_ID from one of the tables. > > > >Why would someone change the behavior of something so drastically? > >This will be affecting code everywhere. > >-- > > > >Bud Schneehagen - Tropical Web Creations, Inc. > > > >_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ > >Web Based Solutions / eCommerce Development & Hosting > >http://www.twcreations.com/ - http://www.cf-ezcart.com/ > >Toll Free: 877.207.6397 - Local & Int'l Phone/Fax: 386.789.0968 > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260937 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
