multiple databasejoin to same table

[susannanam]

hi all,

in one list i have four databasejoin fields and all have the same table as base. all should have the same where clause (where sh2m8_term.ccm = 0). as soon as i put this where clause to the second field, i get t his error:
Unknown column 'sh2m8_term.ccm' in 'where clause' SQL=SELECT DISTINCT(`sh2m8_term_0`.`term_id`) AS value, `term_id` AS text FROM `sh2m8_term` AS `sh2m8_term_0` WHERE sh2m8_term.ccm = 0 ORDER BY text ASC

as long as there is max one where clause involved, the handling of the four fields works perfectly fine. only the second where-clause will fail.

susanne

 

[cheesegrits]

Did you read the popup help? The bit about using {thistable} if you have more than one join to the same table.

When you have more than one join to the same table, we have to use table aliases to avoid name clashes, as each join has to have a unique name. So we append an incrementing number, like this ...

LEFT JOIN your_table AS your_table_0 [blah blah]
LEFT JOIN your_table AS your_table_1 [blah blah]

You can see this in the error message you quoted, where the FROM is `sh2m8_term` AS `sh2m8_term_0`.

So ... if you add a WHERE clause, the table name you use has to match the alias, because when a table has an AS alias, as far as the query is concerned, the original table name doesn't exist, it can only be referenced as the alias.

The problem of course is that you (or we) don't know in advance what that alias will be, as it depends how many joins you add to the table. The alias doesn't get computed until runtime, i.e. when the page is loading and we build the queries.

So ... we provide that {thistable} placeholder, which we will replace with the current alias for the WHERE clause. So in that first one you have the error msg for, we would replace {thistable} with sh2m8_term_0.

-- hugh

 

[susannanam]

Did you read the popup help? The bit about using {thistable} if you have more than one join to the same table.

When you have more than one join to the same table, we have to use table aliases to avoid name clashes, as each join has to have a unique name. So we append an incrementing number, like this ...

LEFT JOIN your_table AS your_table_0 [blah blah]
LEFT JOIN your_table AS your_table_1 [blah blah]

You can see this in the error message you quoted, where the FROM is `sh2m8_term` AS `sh2m8_term_0`.

So ... if you add a WHERE clause, the table name you use has to match the alias, because when a table has an AS alias, as far as the query is concerned, the original table name doesn't exist, it can only be referenced as the alias.

The problem of course is that you (or we) don't know in advance what that alias will be, as it depends how many joins you add to the table. The alias doesn't get computed until runtime, i.e. when the page is loading and we build the queries.

So ... we provide that {thistable} placeholder, which we will replace with the current alias for the WHERE clause. So in that first one you have the error msg for, we would replace {thistable} with sh2m8_term_0.

-- hugh

thank you Hugh ))))))))))) now it works fine!