2 Replies Latest reply on Aug 30, 2007 10:11 AM by cf_dev2

    Group 2 Tables Together

    GudTimz
      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?

        • 1. Re: Group 2 Tables Together
          Dan Bracuk Level 5
          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.
          • 2. Re: Group 2 Tables Together
            cf_dev2 Level 1
            > 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 ...