Copy link to clipboard
Copied
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.
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 - a
...Copy link to clipboard
Copied
SQL joins would be my first choice.
Copy link to clipboard
Copied
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...
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.