Best approach to join multiple statistics tables into one

[MarcovandenOever]

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.

[ilssac]

SQL joins would be my first choice.

[MarcovandenOever]

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...

[insuractive · Accepted solution]

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.

[MarcovandenOever]

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.

[insuractive]

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.

[MarcovandenOever]

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.