Query of Queries

[Olivia_Crazy_Horse]

We finally upgraded from cf 5 to cfmx and I am using query of queries for the first time.

I have to join six tables together to get the info I need and that is too complex for me. I tought of just joining two, then use query of queries to join the third, then use another to join the fourth, etc. but the examples and cf documnetation does not allow joins for query of queries.

Other than buiding tables to join, is there a subquery or subfile to extract and hold the first two joins, then join to the third table, etc ? Or amy I missing somehting and query of queries can do it ?

[paross1]

quote:

---

I have to join six tables together to get the info I need and that is too complex for me

---

Whoa, just because it is "too complex" for you doesn't mean that you should then do something that is the wrong way of doing things. Q-of-Q is good for combining data from queries from different data sources and also for selecting a sub set of data from a previous query, etc., but it shouldn't be used if the method of joining your data should more appropriately be done in your database query. I routinely join dozens of queries, using inner and outer joins, and it isn't that complicated if you understand your data model and the underlying relationships of your entities.

How about posting your data structure and what it is that you are trying to accomplish, and perhaps someone can point you in the right direction?

Phil

[Dan_Bracuk]

In addition to what paross1 said, there are some limitations to QofQ.

You can only select from 2 queries at a time.
You can't use the join keyword. You do inner joins in the where clause and you have to be creative if want to do outer joins.
and a few more.

[insuractive]

If you are just joining data from different tables, without aggregating any of the data, then you should be able to do what you want using a combination of outer and inner joins like Phil suggested.

If its a little more complicated than that, you might be better off using temporary tables on your database to prepare the data you need. If this is a query that needs to be run often, I would also look into using stored procedures or views in order to speed up the process.

[paross1]

....or perhaps even create views in your database using more complex SQL so that the queries that you write in ColdFusion can be less complicated.

Phil

[trojnfn]

When the cf query runs using views, do the views have to be created each time, or just once and the query uses it over and over ?

[Dan_Bracuk]

When a view has been created, it can be queried until the cows come home, or until someone drops it.