SQL error 1052 - [value] Column [column name] in where clause is ambiguous

[Parisi]

I am getting this error when running an advanced search on a list in Fabrik. Here is short version of the scenario:

• I have multiple databasejoin elements in the main list ("Abe") joined to another, single table ("Ben"), in order to display values from Ben in the Abe list.
• There is also a third table ("Carl") joined to the Abe list in one databasejoin element.
  
• In the generated SQL that Fabrik shows when displaying the error, the "where" clause is referencing a column of Ben without specifying which table the column is in (which I believe is causing the ambiguity error).

More details:

The Ben table is a "lookup" tool for the elements in the Abe table. In other words, some of the columns in the Abe table store a standardized shortcode, and the Ben table contains those shortcodes and their corresponding meanings in human-readable English. Databasejoin elements to Ben in the Abe list allow Fabrik to display the English to Fabrik users, rather than displaying the shortcodes.

We will call the three utilized columns from Ben:

1. shortcode - contains the shortcodes from Abe
2. translation - the readable English that Fabrik displays
3. columnlookup - contains the names of the columns in Abe that are databasejoin elements

Error thrown:

Here is the error + query Fabrik gives from an advanced search on the Abe list. This search is run on one the elements in Abe that is databasejoined to Ben (the search would be: WHERE abe.column2 EQUALS somerealvalue):

500 filter query error: column2 Column 'columnlookup' in where clause is ambiguous SQL=
SELECT DISTINCT (`ben_0`.`translation`), `abe`.`column2`
FROM `abe`
LEFT JOIN `carl` ON `carl`.`id` = `abe`.`carl_id`
LEFT JOIN `ben` AS `ben` ON `ben`.`shortcode` = `abe`.`column1`
LEFT JOIN `ben` AS `ben_0` ON `ben_0`.`shortcode` = `abe`.`column2`
WHERE `abe`.`column2`
    IN ([the list of the values from ben.shortcodes where ben.columnlookup = abe.column2 is produced here by Fabrik])
    AND columnlookup = 'column2'

Need some help here. Not sure if this is because Fabrik is not giving specificity where it is needed or because of a way I have joins set up.

 

[cheesegrits]

Impossible to tell from a description.

Which site / list is it on, and what do I need to do to provoke the error.

-- hugh

 

[Parisi]

It is the SHIELD site under my profile.

1. Log in from Front End (top-right menu item).
2. Go to "Database Edits" -> "All Project Data"
3. Do an advanced search for: Type CONTAINS Announced

Thanks.

 

[cheesegrits]

Try now.

I've gone through and fixed quite a few of your join elements that have WHERE filters, where you haven't used the {thistable} syntax. As per the tooltip, you have to use {thistable}.field_name to dis-ambiguate field names, if you have multiple joins to the same table in your form/list, or joins to tables that have the same field names in them.

So for example, the Project Data Type join has to have ...

WHERE {thistable}.dropdown_name = 'project_data_type' ORDER BY {thistable}.dropdown_label ASC

We then replace {thistable} with whatever alias we are using for the table in that query.

You will probably have to go through the rest of them and do that, to avoid similar issues in other queries.

-- hugh

 

[Parisi]

Yes, this is working now, and the {thistable} syntax usage makes sense. Thank you!