6 Replies Latest reply on May 24, 2009 7:54 AM by MarcovandenOever

    Best approach to join multiple statistics tables into one

    MarcovandenOever Level 1

      I have read different approaches to join multiple statistics tables into one, they all have a column "productobjectid".

      I want to get all data for each product and put it to excel and output a jpg statistic with cfchart.

       

      How would you do this, the sql part?

       

      Thanks.

        • 1. Re: Best approach to join multiple statistics tables into one
          ilssac Level 5

          SQL joins would be my first choice.

          1 person found this helpful
          • 2. Re: Best approach to join multiple statistics tables into one
            MarcovandenOever Level 1

            Could you give a example, i try to use a multiple join but keep running into a "Column blabla in from clause is ambiguous" error...

            • 3. Re: Best approach to join multiple statistics tables into one
              insuractive Level 3

              Your "abiguous" error is caused when you are referencing multiple tables in your SQL statement that have the same field name.

               

              e.g.

               

              TableA has field "ID"

              TableB also has a field called "ID"

               

              If your select statement was:

               

              SELECT *

              FROM TableA inner join TableB on TableA.myField = TableB.myField

              WHERE ID = 4

               

              then SQL would freak out because it wouldn't know which ID field in which table you are trying to access.  Your best bet is to always prefix your field names with the name of your table (or an alias - aliases are very useful if you have long table names) when doing a Select statement.

              • 4. Re: Best approach to join multiple statistics tables into one
                MarcovandenOever Level 1

                Awesome, still a question, i now have the following code:

                 

                SELECT * FROM shopproductbehaviour_views
                INNER JOIN shopproductbehaviour_sails
                ON shopproductbehaviour_views.productobjectid
                WHERE shopproductbehaviour_sails.productobjectid = '#all.productobjectid#'

                 

                How should i ad 2 more tables "shopproductbehaviour_ads" and "shopproductbehaviour_dels"?

                 

                And also, when al this info is in one query, how can you address all those different values?

                 

                I need to get 2 values from each table, the "datetimecreated" and "productobjectid" values from the same names columns.

                • 5. Re: Best approach to join multiple statistics tables into one
                  insuractive Level 3

                  A couple suggestions:

                   

                  1) when joining tables, its best to list both table/fields that you are joining in the FROM clause:

                   

                  FROM shopproductbehaviour_views INNER JOIN shopproductbehaviour_sails ON shopproductbehaviour_views.productobjectid = shopproductbehaviour_sails.productobjectid

                   

                  2) You add tables to a SQL join by placing another join statement after the SQL above:

                   

                  SELECT *

                  FROM TableA INNER JOIN TableB on TableA.myField = TableB.myField

                  INNER JOIN TableC on TableA.anotherField = TableC.anotherField

                   

                  3) If you have columns in the tables that are named the same, you can use column aliases to change the way they appear in your record set:

                   

                  SELECT TableA.datetimecreated 'tablea_create_date', TableB.datetimecreated 'tableb_create_date'

                   

                  4) Certainly not a requirement, but you might want to look into using <cfqueryparam> in your where clause:

                   

                  WHERE shopproductbehaviour_sails.productobjectid = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#all.productobjectid#">

                   

                  You might want to consider checking out one of the many tutorials on SQL available online.  Many of the questions you posed in your post are covered in pretty much every basic SQL tutorial.  Alternately, a good SQL book is worth its weight in gold for a beginning web applications developer.

                  1 person found this helpful
                  • 6. Re: Best approach to join multiple statistics tables into one
                    MarcovandenOever Level 1

                    Thanks so much for your help, you're right it's time to buy a good sql book to study these kind of stuff

                     

                    I just ordered the following book (for the next one looking for answers):

                     

                    Sams Teach Yourself SQL in 10 Minutes

                     

                    http://www.amazon.com/exec/obidos/tg/detail/-/0672325675

                     

                    Update 05-24-09:

                     

                    Just finished this book, it's really worth to read through it and as reference.