Group 2 Tables Together

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GudTimz - 30 Aug 2007 15:59 GMT

I have two tables, almost identical with the exception of a couple fields for
two separate departments. The fields I am querying are fields present in both
tables.

I am wanting to group the two tables together, and output their results
grouped together into one report.

How would one do this without getting ambiguous errors?

<CFQUERY name="q1">
SELECT avendor,yvendor,color,size,sku,orderdate
FROM table1, table2
WHERE orderdate >= #submitted.startdate# AND orderdate <= #submitted.enddate#
</CFQUERY>

<CFOUTPUT query="q1" group="avendor">
#avendor#
<CFOUTPUT group="sku">
<CFOUTPUT group="color">
<CFOUTPUT group="size">
<CFOUTPUT>
#orderid# - #sku# - #color# - #size#
</CFOUTPUT>
</CFOUTPUT>
</CFOUTPUT>
</CFOUTPUT>
</CFOUTPUT>

<CFOUTPUT query="q1" group="yvendor">
#avendor#
<CFOUTPUT group="sku">
<CFOUTPUT group="color">
<CFOUTPUT group="size">
<CFOUTPUT>
#orderid# - #sku# - #color# - #size#
</CFOUTPUT>
</CFOUTPUT>
</CFOUTPUT>
</CFOUTPUT>
</CFOUTPUT>

Reply to this Message

Dan Bracuk - 30 Aug 2007 17:29 GMT

First, you have to join your tables on some field, otherwise you will get way
more records than you want.

Next, any field that has the same name in each table has to be qualified by
the tablename or an alias thereof in your query.

Reply to this Message

cf_dev2 - 30 Aug 2007 18:11 GMT

> SELECT avendor,yvendor,color,size,sku,orderdate
> FROM table1, table2
> WHERE orderdate >= #submitted.startdate# AND orderdate <= #submitted.enddate#

As mentioned, the query doesn't indicate how table1 and table2 are related.
So the result is a CROSS JOIN. Probably not what you want to do
http://en.wikipedia.org/wiki/Join_(SQL)#Cross_join

Its a good practice to always specify the column source when using JOINS,
either using an "alias" or the full table name. Even if its not always
required, it increases readability.

SELECT table1.avendor,
table1.vendor,
table1.color,
...
FROM table1 INNER JOIN table2
ON table1.someSharedColumn = table2.someSharedColumn
WHERE ...

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / August 2007

Group 2 Tables Together