invalid reference to FROM-clause entry for table "t"-CodePudding

I have wrote this join statement in sql and triple checked everything and have tried multiple things but can not seem to shake this bug.-- The Talent Acquisition team is looking to fill some open positions.

They want you to get them the territory_description and region_description for territories that do not have any employees, sorted by territory_id.

Let's tackle this one piece at a time.

In order to achieve this result set, we will need to join the territories and regions table together. So first, select the territory_description column of the territories table, aliased as t, and the region_description of the regions table, aliased as r.

Write a FROM statement for the territories table. Alias it as 't' as you do so.

Then, write a JOIN statement, joining the regions table, aliasing it as 'r'.

This JOIN should find records with matching values ON region_id in the territories and regions tables.

If you run the query you've constructed at this point, you should see a result set that contains territory descriptions and corresponding region descriptions.

But we're not done! We want only records WHERE the territories do not have any employees.

Below the JOIN statement, write a WHERE statement to create a subquery. Find WHERE the territory_id in the territories table is NOT IN the result set from a subquery that selects the territory_id from the employee_territories table.

Finally, take the final result set and order by territory_id.

SELECT t.territory_description, r.region_description, t.region_id, r.region_id 
FROM territories t, regions r
JOIN regions
ON t.region_id = r.region_id

ERROR:  invalid reference to FROM-clause entry for table "t"
LINE 29: ON t.region_id = r.region_id
            ^
HINT:  There is an entry for table "t", but it cannot be referenced from this part of the query.
SQL state: 42P01
Character: 1524

I have tried references, removing the region ids from the select statements, and even changing some aliases around or putting different tables in the from statement but nothing seems to work.

CodePudding user response：

Your SQL query is not structured right - you are trying to do both an implicit JOIN via , regions r and an explicit JOIN via JOIN regions.

Here's a corrected version of your query that just uses the explicit JOIN:

SELECT t.territory_description, r.region_description, t.region_id, r.region_id 
FROM territories t
JOIN regions r
ON t.region_id = r.region_id