Tony, I don't believe that is the case. I'm not sure about your classes, but when you use the "*", SQL server must look up the column names in the system tables in order to create an execution plan. I suppose there might be some pre-cached plan for the asterick for each table. In my own testing I have found that tables with lots of columns always perform better if I use the column names, but perhaps that is because I'm using less than half. Also, in practice I never select "text" columns from a table unless I absolutely need them. In any case, I've heard SQL gurus say that using the column names is good practice and more efficient. Perhaps we could run down the truth on MSDN.
-mk -----Original Message----- From: Tony Weeg [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 11:16 AM To: CF-Talk Subject: RE: Two tables same column name? correct me if im wrong, but in my sql server classes I was led to believe that if you were going to do perform a query, and the number of columns that you had to specify was over half the total amount of columns in the actual table, that it would be better to use "*" vs. naming them all out..... .tony Tony Weeg Senior Web Developer Information System Design Navtrak, Inc. Fleet Management Solutions www.navtrak.net 410.548.2337 -----Original Message----- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 12:03 PM To: CF-Talk Subject: RE: Two tables same column name? John, Not to pontificate but.... using the specific column names is usually a wise idea in any case. Using the asterick can have an impact on performance, since the DB has to look them up - and you end up with a query that has data in it that you do not need. Using the column names makes you code more maintainable for others as well - since they can see exactly what columns are being pulled from the DB without going to the db. In practice, I only use the asterick for very predictable, small "lookup" tables that contain fairly static data (like a table of state names and codes .... tblState.Name, tblState.Code). That being said, you can get half way there by using an asterick for one of the tables, and specifying columns and aliases for the other - as in SELECT A.*, b.total AS Total2 , B.Average AS avg2 Just make sure that any duplicate column name for the "non-wildcard" table is aliased to avoid "ambiguous column" type errors. -mk -----Original Message----- From: John Gedeon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 10:22 AM To: CF-Talk Subject: RE: Two tables same column name? I guess that is the only way to do that. I was hoping not to have to list all the columns I needed but to use the *. Thanks any way guys :) At 09:49 AM 9/24/2002 +0100, you wrote: >hi john >try aliasing the value you want to pull out, there is a way which will >negate the need to name all the columns you want to select, but I can't >remember it off the top of my head. The code below should work, let me kow >if you have any problems > >Mark > ><cfquery name="getRep" > datasource="#session.datasource#" > dbserver="#session.dbserver#"> > SELECT b.total, d.total AS second_total > FROM trip_flow_breakdown b, trip_flow_data d > WHERE b.wid = #attributes.wid# AND b.fid = #attributes.fid# AND b.id = >#attributes.id# > AND d.wid = #attributes.wid# AND d.fid = #attributes.fid# ></cfquery> > ><cfoutput>#getRep.second_total#</cfoutput> > >-----Original Message----- >From: John Gedeon [mailto:[EMAIL PROTECTED]] >Sent: 24 September 2002 01:21 >To: CF-Talk >Subject: Two tables same column name? > > >I have a query where i join two tables. and both tables have a field which >are titled the same but each table has a different value. is there a way >for pulling the two different values out in cold fusion or do i have to use >multiple selects?? > ><cfquery name="getRep" > datasource="#session.datasource#" > dbserver="#session.dbserver#"> > SELECT * > FROM trip_flow_breakdown b, trip_flow_data d > WHERE b.wid = #attributes.wid# AND b.fid = #attributes.fid# AND b.id = >#attributes.id# > AND d.wid = #attributes.wid# AND d.fid = #attributes.fid# ></cfquery> > > the field i want to get is total but if i use > <cfoutput query="getRep">#total#</cfoutput> i only get the value of the >first table in the from clause. any one have a solution? > ><>< Proverbs 3:5 "Trust in the Lord with all your heart and lean not on >your own understanding;" > > > ______________________________________________________________________ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
