Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-30 : 09:24:28
|
| Sabapathy writes "Database: NorthwindQuery: select * from products where categoryid in (select categoryid from suppliers)The above query works fine. But, in the sub query, the "suppliers" table does not have the field "categoryid". If I execute only the sub query,it gives me the error,"Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'categoryid'."I think, the sub query is taking the categoryid field from the product table itself not from suppliers table.I don't know what is the logic behind this. It will be greateful if you could solve this for me at the earliest.Looking forward for your earliest reply.Thanks with regards,Sabapathy" |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-07-30 : 10:03:30
|
quote:
I don't know what is the logic behind this.
The logic behind what? Someone writing that code or it being valid?It's valid because correlated subqueries are not forced to return values from within the subquery. I doubt that anyone would do it deliberately, although in the instance you give it's difficult to see why someone would mistakenly think that Suppliers had a CategoryID column comparible to Products.CategoryID.If the intent is to return 0 rows if Suppliers has no rows,SELECT * FROM ProductsWHERE EXISTS (SELECT * FROM Suppliers) AND CategoryID IS NOT NULLwould seem less prone to misinterpretation. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-07-30 : 10:30:26
|
| there's also a problem in your statement...."The above query works fine" as it isn't 100% true....1. yes it executes....2. but does it return the correct result set?...because "select * from products" returns the exact same resultset....there's a difference between "something running" and "Something running and producing the right results"....SQL does seem to have a problem with the subquery....in that column names that will allow the query to run can be from either table....but the inner name must come from some table....a (select dummyname from suppliers) subquery won't work at all...It's not great (or in fact is very poor) that it runs this way...because it can lead to a presumption that the code works....but thats what 'expected results' are there to find out!!maybe it's one for Microsoft to resolve..... |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-07-30 : 10:40:04
|
quote:
because "select * from products" returns the exact same resultset....
For the data in Northwind it does. But as I pointed out, rows where Products.CategoryID IS Null would be excluded, and if Suppliers contains no rows, then there will be no rows in the query's result. |
|
|
|
|
|
|
|
|
Problem with executing sub query