Ask Ben: Adding A Column From Second Query To First Query

I have two queries and I want to add a column from the second query to the first query. What is the best way to do this?

First off, I just want to say that I am not sure why you would want to do something like that, so maybe (likely?) I am misunderstanding what you mean. If you are talking about joining the two queries and selecting all columns from the first and only one column from the second, that can be done using a simple ColdFusion query of queries which supports inner joins and full outer joins. If you are not talking about that, the only other option is that you simply want to add one column, in order, to the other column.

Since I have no idea what you are talking about, let's quickly look at both. But first, let's build two queries with which to experiment:

Now we have two queries: one has a column of letters and the other has a column of numbers (both have an identifier column in order to mimic a more real-world situation). If we go the query of queries route, it would look something like this:

Notice that in our ColdFusion query of queries, we are not specifying any join condition. This acts like a FULL OUTER JOIN and produces the cartesian product of all joined query columns:

This adds the entire column of the second query to every row of the first query. Not sure how useful this would be to you unless you included a join condition (in the WHERE clause).

If you actually mean just adding appending a column of one query to the existing columns of another query, well, this is quite easy. If you look above at how we created the original test queries, you might get an idea as to how we are going to do this - just add the column:

Note that we added the column using a simple QueryAddColumn() method. ColdFusion rocks so hard core! Also note that I needed to refer to the second query's column using array notation:

qTwo[ "number" ]

This is required. Remember, ColdFusion is used to getting cell values from a query - it's not used to referencing the entire record set column. If you were to add this:

qTwo.number

... to the first query, ColdFusion would assume you are trying to pass in the value "1" which is the first column value. Doing so would throw the error:

Object of type class java.lang.String cannot be used as an array

That being said, performing the above action results in the following output:

This outcome here is quite different than the outcome of the ColdFusion query of queries. Using QueryAddColumn() simply appends the column. It doesn't change the order of either query not does it check any join information; it simply adds the column.

Again, I am not sure what exactly you were going for. The ColdFusion query of queries method seems the more useful of the two, but it didn't sound like what you were trying to do. The QueryAddColumn() is quite literal, but as far as I can see, not very useful (in this type of a scenario). But, if you can find a use for it, I would be excited to see it.

What Other People Are Searching For

[ local search ] add one column to a query coldfusion
[ local search ] move column from one query to another