Re: [SQL] Order of items in FROM causes error?
Rosser Schwarz <[EMAIL PROTECTED]> writes: > A colleague has the following query, which errors with: relation "dl" > does not exist. (See the second item in the FROM clause.) If that > item is moved to immediately precede the first JOIN item however, the > query works as expected. > select u.usersfirstname || ' ' || u.userslastname as userssupervisorsname > , l.locationsname || ' >> ' || lv.listvaluesname as locationdepartment > , lvcat.listvaluesname as usersemploymentcategory > , lvclass.listvaluesname as usersemploymentclass > , lvacdcat.listcategoriesname as usersacdcategory > from intranet.tbl_users u > , intranet.tbl_departmentslocations dl > , intranet.tbl_listvalues lvcat > , intranet.tbl_listvalues lvclass > , intranet.tbl_listcategories lvacdcat > join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid > join intranet.tbl_locations l on dl.locationsid = l.locationsid > where u.usersid = 199 >and dl.listvaluesid = 13 >and lvcat.listvaluesid = 23 >and lvclass.listvaluesid = 27 >and lvacdcat.listcategoriesid = 6 > This strikes me as a bug. No, it isn't, because JOIN binds more tightly than comma. (People coming from MySQL tend to misunderstand this, because MySQL gets it wrong...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Order of items in FROM causes error?
On Fri, 1 Apr 2005, Rosser Schwarz wrote: > A colleague has the following query, which errors with: relation "dl" > does not exist. (See the second item in the FROM clause.) If that > item is moved to immediately precede the first JOIN item however, the > query works as expected. > > select u.usersfirstname || ' ' || u.userslastname as userssupervisorsname > , l.locationsname || ' >> ' || lv.listvaluesname as locationdepartment > , lvcat.listvaluesname as usersemploymentcategory > , lvclass.listvaluesname as usersemploymentclass > , lvacdcat.listcategoriesname as usersacdcategory > from intranet.tbl_users u > , intranet.tbl_departmentslocations dl > , intranet.tbl_listvalues lvcat > , intranet.tbl_listvalues lvclass > , intranet.tbl_listcategories lvacdcat > join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid > join intranet.tbl_locations l on dl.locationsid = l.locationsid > where u.usersid = 199 >and dl.listvaluesid = 13 >and lvcat.listvaluesid = 23 >and lvclass.listvaluesid = 27 >and lvacdcat.listcategoriesid = 6 > > This strikes me as a bug. Is it known behavior? A quick search > doesn't turn up much, but that may be a weakness in my google-fu. SQL seems to say that join binds more tightly than commas, so I don't believe dl is in scope for either of those ON clauses in the explicit join syntax. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Order of items in FROM causes error?
A colleague has the following query, which errors with: relation "dl" does not exist. (See the second item in the FROM clause.) If that item is moved to immediately precede the first JOIN item however, the query works as expected. select u.usersfirstname || ' ' || u.userslastname as userssupervisorsname , l.locationsname || ' >> ' || lv.listvaluesname as locationdepartment , lvcat.listvaluesname as usersemploymentcategory , lvclass.listvaluesname as usersemploymentclass , lvacdcat.listcategoriesname as usersacdcategory from intranet.tbl_users u , intranet.tbl_departmentslocations dl , intranet.tbl_listvalues lvcat , intranet.tbl_listvalues lvclass , intranet.tbl_listcategories lvacdcat join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid join intranet.tbl_locations l on dl.locationsid = l.locationsid where u.usersid = 199 and dl.listvaluesid = 13 and lvcat.listvaluesid = 23 and lvclass.listvaluesid = 27 and lvacdcat.listcategoriesid = 6 This strikes me as a bug. Is it known behavior? A quick search doesn't turn up much, but that may be a weakness in my google-fu. We're running 7.4.2. (Yes, I know; I'm about to start testing 8.0.2 on our new server.) /rls -- :wq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
