如何解决Metabase中‘missing FROM-clause entry for table’错误

Let's break down your issue and fix it step by step:

Why This Error Happens #

The error message ERROR: missing FROM-clause entry for table "bellhop_order_relationship" means PostgreSQL can't find a valid reference to the bellhop_order_relationship table in your FROM/JOIN clauses. Even though you've aliased it as bor, there are a few common reasons this happens:

• You might have accidentally used the full table name (bellhop_order_relationship) instead of its alias (bor) somewhere in your query (like a WHERE clause you didn't include in your snippet).
• There could be a typo in the table name or alias definition (e.g., misspelling bellhop_order_relationship or forgetting to add as bor after the table name).
• Less commonly, the table might not exist in the _gospel schema, or you don't have permission to access it.

Corrected Query #

We'll fix the immediate error and also address a second issue you'll hit next: using aggregate functions (count, avg) without a GROUP BY clause (PostgreSQL requires this by default to know how to group your aggregated results).

Here's the revised SQL:

select 
    bor.bellhop_full_name, 
    count(bor.order_id) as Moves, 
    avg(orders.order_review_score) as Order_Rating 
from _gospel.bellhop_order_relationship as bor 
join _gospel.orders on orders.order_id = bor.order_id 
join _gospel.incidents on orders.order_id = incidents.order_id
group by bor.bellhop_full_name;

Additional Checks to Confirm #

1. Verify Table Existence: Run this query to make sure the table exists in the _gospel schema:

   select table_name 
   from information_schema.tables 
   where table_schema = '_gospel' 
     and table_name = 'bellhop_order_relationship';
   

   If no results come back, you've either misspelled the table name or it doesn't exist in that schema.

2. Check for Unaliased References: If you added extra clauses (like WHERE or HAVING) to your original query, double-check that you're using the alias bor instead of the full table name whenever you reference columns from bellhop_order_relationship.

内容的提问来源于stack exchange，提问作者Nnamdi