SQL Apprentice Question
Why does this query work; 
SELECT e1.emp_no, e1.emp_lname, e1.domicile, d1.location 
FROM employee_enh e1 JOIN employee_enh e2 
ON e1.domicile = e2.domicile 
JOIN department d1 JOIN department d2 
ON d1.location = d2.location 
ON e1.dept_no = d1.dept_no 
WHERE e1.emp_no <> e2.emp_no 
but this one does not?; 
SELECT e1.emp_no, e1.emp_lname, e1.domicile, d1.location 
FROM employee_enh e1 JOIN employee_enh e2 
ON e1.domicile = e2.domicile 
JOIN department d1 JOIN department d2 
ON e1.dept_no = d1.dept_no 
ON d1.location = d2.location 
WHERE e1.emp_no <> e2.emp_no 
All statements contained in both queries are identical. The change 
between the two is that the order of the last two ON statements are 
switched. 
Query Analyzer gives me the following error; 
The column prefix 'e1' does not match with a table name or alias name 
used in the query. 
What is the rule? 
Celko Answers
>> What is the rule? << 
Infixed joins are executed from left to right.  The ON clause (if any) 
is associated with the most recent JOIN clause.  Parentheses are 
executed in their order of nesting.  No great surprises here -- pretty 
much like other scoping rules in 3GLs 
A derived table can be constructured with parens and an AS clause. 
Only the alias is available to containing queries, not the contained 
table names.  This catches people.
Thursday, August 31, 2006
Subscribe to:
Post Comments (Atom)

No comments:
Post a Comment