June 14, 2015 at 5:54 am
Comments posted to this topic are about the item How Many Rows Returned
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
June 14, 2015 at 9:16 am
Fairly Simple. Got 1 mark.
Thanks.
June 15, 2015 at 1:59 am
Came down to a choice between 0/Error and 9, but since the correct answer (on a case sensitive database) wasn't there, there was only one choice 🙂
June 15, 2015 at 2:24 am
I had a little hesitation about the NULL values, but I gambled it right !
Thanks.
June 15, 2015 at 2:53 am
This was removed by the editor as SPAM
June 15, 2015 at 4:49 am
Nice, simple and straightforward. Thanks.
June 15, 2015 at 6:07 am
nice question. thanks for sharing
June 15, 2015 at 10:43 am
It's a nice question, but I think the explanation lacks something.
An explanation of how each of the three joins leaves the number of "Jim rows" alone, the first join leaves the number of "Job rows" alone too since the grouping to form the derived table aliased b0 merges the two "Job rows" into one, while the two joins with #B (aliased b1 and b2) each double the number of "Job rows" because there are two "Job rows" IN #B, and since the beginning is with #A which has 2 "Job rows" and 1 "Jim rows" the number of "Jm rows" is 1 after each of teh joins while the number of "Job rows" starts at 2, is still 2 after the first join, is 4 after the second join, and 8 after the third so that after the third join there are 9 rows althogether might be useful to people who don't understand joins particularly well - although it would probably have to expressed a bt more clearly than I've put it.
Tom
June 15, 2015 at 11:01 am
I needed a coffee break... Nice. Thanks, Gary!
June 15, 2015 at 12:29 pm
For anybody having trouble following the joins, just focus on the Authors in the individual joins.
First #A has 4 authors: ('Job', 'Job', 'Jim', NULL)
The subquery takes this same set, but groups by author, combining both 'Jobs'. So this gives ('Job', 'Jim', NULL)
Since the first #A and the subquery are joined by an INNER JOIN (JOIN default to INNER unless OUTER or CROSS are specified), both 'Job's from part 1 match to the 'Job' in the subquery, and 'Jim' matches 'Jim'. The NULLs are eliminated by the INNER JOIN because the values are unknown and therefore can never match another value, even another NULL. So the result after the first JOIN is now only 3 items: ('Job', 'Job', 'Jim')
The second JOIN matches the previous ('Job', 'Job', 'Jim') to #B ('Job', 'Job', 'Jim', NULL). Again, the NULL doesn't match and gets eliminated, but now because there are 2 'Job's on the left and 2 'Job's on the right, each 'Job' joins to both of the others, giving 4 'Job's. So after the second JOIN, we now have ('Job', 'Job', 'Job', 'Job', 'Jim')
The final join takes this ('Job', 'Job', 'Job', 'Job', 'Jim') and joins again to ('Job', 'Job', 'Jim', NULL). Same thing as previous step, only this time, the 4 'Job's on the left each join to both 'Job's on the right, giving 8 'Job's plus the 1 'Jim'. So the final result set includes 8 'Job's and 1 'Jim'. ('Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Jim')
June 15, 2015 at 5:30 pm
NBSteve (6/15/2015)
For anybody having trouble following the joins, just focus on the Authors in the individual joins. ...(Nice explanation deleted) ...
That was a very good explanation. I appreciate it.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
June 15, 2015 at 11:53 pm
Thanks NBSteve, that is how one should explain this.
June 16, 2015 at 4:45 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 20, 2015 at 11:16 am
Nice one..thanks 4 sharing:-)
June 25, 2015 at 6:45 am
Yep, you have to be careful with joins that you know exactly the kinds of connections you can get.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply