May 21, 2015 at 5:01 am
Easy one. A practice exam question was about the same behaviour. Once you've spent hours finding out why your answer isn't correct, you tend to never forget it again 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 21, 2015 at 5:35 pm
Shouldn't the subqueries contain a TOP 1 ?
May 21, 2015 at 8:46 pm
Well, if you wanted to make sure that changes to the data wouldn't result in the queries' returning an error because of using = with a subquery that returns more than 1 value, then that would be a way to do it.
With the particular data provided, though, none of the subqueries return more than 1 value anyway, so it's not necessary.
Cheers!
May 22, 2015 at 7:12 am
Revenant (5/20/2015)
Lots of reading, but in the end it was simple. Thanks, Gopi!
Too much reading, in fact! I could have saved a lot of time by reading the answers before the question. But definitely simple. and definitely illustrates the need for using table aliases to qualify all column names if you want your code to read as if it means what it actually means.
Tom
May 22, 2015 at 10:36 am
Rick Harker (5/21/2015)
Shouldn't the subqueries contain a TOP 1 ?
No. It would change the meaning of the queries. As posted, they implement "give me the department ID for Human Resource and make sure to throw a tantrum if there is a data quality issue". Adding TOP 1 changes this to "give me a department ID for Human Resource and if there is a data quality issue, pretend you never noticed and definitely don't tell anyone".
I know which version I prefer. (Allthough in reality, I would (a) not use a subquery for this anyway, but rather a join; and (b) have declared a UNIQUE constraint on the Name column in the Department table to prevent data quality issues.)
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply