CODEWITHSUNDEEP
mysqlcoldfusion
Betty Mock
Betty Mock

Reputation: 1393

MySQL select duplicate columns

I am selecting the columns State and Zip3 twice in my select statement, stating exactly what I want to see. When I do it in native MySQL I get the right result:

SELECT State,Zip3, FirstName, LastName, State, Zip3 
FROM Person
WHERE State >= 'A'
ORDER BY State, Zip3
LIMIT 10;

+-------+------+-----------+-------------+-------+------+
| State | Zip3 | FirstName | LastName    | State | Zip3 |
+-------+------+-----------+-------------+-------+------+
| AB    | T7X  | Adalei    | Starreveld  | AB    | T7X  |
| AK    | 998  | Linda     | Rosenthal   | AK    | 998  |
| AL    | 361  | Benjamin  | Sung        | AL    | 361  |
| AL    | 362  | Lawrence  | Kallus      | AL    | 362  |
| AZ    | 857  | Rose      | Springfield | AZ    | 857  |
| CA    | 941  | Maura     | Cronin      | CA    | 941  |
| CA    | 941  | Dov       | Grunschlag  | CA    | 941  |
| CA    | 943  | Ruth      | Hodos       | CA    | 943  |
| CT    | 055  | Andrew    | Salvo       | CT    | 055  |
| CT    | 064  | DEBORAH   | GRASER      | CT    | 064  |
+-------+------+-----------+-------------+-------+------+

When I do it in Cold Fusion I get a wrong result:

<cfquery name = "stest" datasource = "Moxart">
select State,Zip3, FirstName,LastName,State,Zip3 from Person
where State >= 'A'
order by State,Zip3
</cfquery>
<cfset j = 0>
<table>
<cfoutput query = "stest">
<tr>
<cfloop index = "col" list = "#columnlist#">
<cfset j = j + 1>
<cfset colname[j] = #stest[col][currentrow]#>
<td>   #colname[j]# </td>
</cfloop><br/>
</tr>
</cfoutput>
</table>


Adalei      Starreveld      AB      AB      T7X     T7X  
Linda       Rosenthal       AK      AK      998     998 
Benjamin    Sung            AL      AL      361     361
Lawrence    Kallas          AL      AL      362     362
Rose        Springfield     AZ      AZ      857     857
Maura       Cronin          CA      CA      941     941
Dov         Grunschlag      CA      CA      941     941
Ruth        Hodos           CA      CA      943     943
Andrew      Salvo           CT      CT      055     055
DEBORAH     GRASER          CT      CT      064     064

How can I get the correct result out of ColdFusion. I am using ColdFusion 8 and Firefox.

Upvotes: 2

Views: 451

Answers (4)

FJT
FJT

Reputation: 2083

Try giving the columns an alias

select 
  State,
  Zip3, 
  FirstName,
  LastName,
  State AS State_2,
  Zip3 as Zip3_2 
from 
  Person
where 
  State >= 'A'
order by 
  State,
  Zip3

Upvotes: 6

Leigh
Leigh

Reputation: 28873

I did try the alias thing, didn't work. But did solve the problem, which was the variable columnlist over which I am looping.

Maintaining column Order

Well you did not actually describe what was wrong with the output in your original question ;) However, as I mentioned in the comments, columnList always uses alphabetical order. To obtain the original order either

  1. Use getMetaData(query). It returns an array of structures containing the query column properties, including name.
  2. Alternately, you could use the undocumented query.getColumnList() method instead. It returns an array of column names (only).

Then simply use a cfloop array. No need to convert to a list, unless you are using MX7 or earlier (which did not support array loops).

    <!--- using getMetaData --->
    <cfset colProps = getMetaData(someQuery) />
    <cfoutput query="someQuery">
        <cfloop array="#colProps#" index="col">
            #someQuery[ col.Name ][ currentRow ]# |
        </cfloop>
        <br />
    </cfoutput>

    <!--- using undocumented getColumnList() --->
    <cfset colNames = someQuery.getColumnList() />
    <cfoutput query="someQuery">
        <cfloop array="#colNames#" index="col">
            #someQuery[ col ][ currentRow ]# |
        </cfloop>
        <br />
    </cfoutput>

there are sometimes good reasons to request a field twice

Unique Query Column Names

Honestly, I cannot think of many good reasons to return the same column multiple times. As Dan pointed out, you could easily output it multiple times. So I am curious as to your use case.

Returning the same column name multiple times in the result is problematic because it is ambiguous. So CF has no idea which column you want when you reference it. In your particular case it does not matter because the values are the same in both. But consider this example where the data values are different, but the same column name is assigned to both:

<cfquery name="someQuery" datasource="someDSN">
    SELECT  1 AS Zip3, 2 AS Zip3 
    UNION ALL
    SELECT  8 AS Zip3, 16 AS Zip3 
</cfquery>

If you were to output #zip3#, what should the correct result be? If you actually dump the query the results are:

  RESULTSET query 
  Row  | ZIP3 | ZIP3 
  1    | 1    | 1  
  2    | 8    | 8

Because CF has no idea which values to use for "Zip3", it apparently just picks the first column it finds to resolve the ambiguity. The values in the second "Zip3" column are simply discarded. That is why you should always use an alias to ensure query column names are unique.

Of course that is assuming it even works in other versions. I vaguely remember something about some versions throwing an error when duplicate column names are encountered. But as it is not something I normally do, I would have to run tests to verify it.

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20794

I suggest selecting once, displaying twice. Something like this:

<cfscript>
Headers = "State,Zip Code,First Name,Last Name,State,Zip Code";
Fields = "state,zip3,FirstName,LastName,State,Zip3";
</cfscript>

<cfquery name = "stest" datasource = "Moxart">
select State, Zip3, FirstName, LastName
from Person
where State >= 'A'
order by State,Zip3
</cfquery>

<table>
<tr>
<cfoutput>
<cfloop index = "thisHeader" List = "#Headers>
<th>#thisHeader#</th>
</cfloop>
<cfoutput>
</tr>
<cfoutput query = "stest">
<tr>
<cfloop list = "#fields#" index = "thisField">
<td>#stest[thisField][currentrow]#</td>
</cfloop>
</tr>
</cfoutput>
</table>

Note that I didn't actually test this code. There may be a syntax error or two but the logic is fine.

Upvotes: 0

MrTechie
MrTechie

Reputation: 1847

Not sure why you are calling it twice but here's how I would do it:

SELECT state, first_name, last_name zip FROM person WHERE state >= 'a' ORDER BY state, zip

If truly zip shows up twice in the table and you want to make sure they match

SELECT state, first_name, last_name zip FROM person WHERE state >= 'a' AND zip = zip ORDER BY state, zip

Upvotes: -2

Related Questions