I've been a bit curious about the syntax of the JOIN operation, especifically the ON clause where we set the condition. Let's say we are performing a join on an existing table and a table we have just created as a subquery in the JOIN, for instance:
SELECT DISTINCT ssn, job, identifier, startdate, enddate, salary, ceasing
JOIN (SELECT DISTINCT name, identifier FROM jobs)
ON oldcontracts.job = jobs.name
The thing here is, how would we go about setting an ON condition that would work? I say this because the syntax is of the following form:
table1.column <condition> table2.column
Where apparently, SQL 2005 is very strict about where we get those columns from. Meaning I get an error on jobs.name saying it is not a valid identifier. I assume SQL throws this error because jobs was not the table that is being joined in the operation.
I know that the way around this is to simply set jobs as the table2 to use in the JOIN, and adjust the SELECT as need be, then everything would work, but I'm curious to know if there's a way to address a column of a subquery, given that that specific subquery is the one being used as table2 in the JOIN operation?