I had a DataGrid in Flex which I wanted to display information from 3 different tables in my MySQL database using a ColdFusion query.

First attempt I tried using Joins etc in MySQL to get all the data and had two problems (1) the column names in the tables were the same i.e. id, company_id, etc and (2) the query data was duplicating the results in the tables. For example if I had 3 records in table 1 and only 1 record in table 2, table 2 would duplicate its records to match the amount returned from table 1?

I am no expert in MySQL but tripping around I could find no quick solution here (except for issue (1) which I solved by using column name aliases in the SELECT statement i.e. image_table.id as imgid).

So onto trusty ColdFusion I went, I broke the whole dilemma up into its most basic format which was essentially 3 queries, one for each table. I next needed to amalgamate the results of these queries to return to flex and this is where the Query of Queries came in (thanks to google and Paul Thornton)! Thanks to that little source I realised how simple it is to just combine the queries and return one fat query back to Flex. job done :)

1
2
3
4
5
6
7
8

<cfquery name="qryMerged" dbtype="query">
    SELECT Col1,Col2,Col3
    FROM request.qryOne
    UNION
    SELECT Col1,Col2,Col3
    FROM request.qryTwo
    ORDER BY Col1
    </cfquery>