1 person found this helpful
SQL joins would be my first choice.
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...
Your "abiguous" error is caused when you are referencing multiple tables in your SQL statement that have the same field name.
TableA has field "ID"
TableB also has a field called "ID"
If your select statement was:
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.
Awesome, still a question, i now have the following code:
SELECT * FROM shopproductbehaviour_views
INNER JOIN shopproductbehaviour_sails
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.
1 person found this helpful
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:
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.
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
Just finished this book, it's really worth to read through it and as reference.