Rob-350472 (4/15/2013)
I figured somebody might know the answer from the 'double join' side of things...Here's a couple of test tables with inserts:
It still works, as in it doesn't throw an error but the data isn't as intended, I'd expect another column duplicating the results of column C1, however, I've created some kind of monster which produces a count of 169 for 'Batemans' rather than 13.
There's something obvious I'm not spotting here which is kind of annoying.
LOL you didn't create a monster. It did exactly what you asked of it. First it joins to #logs which now has 13 rows for 'Batemans'. Then it joins those results to #Items a second time, but this time there are 13 rows to join and 13 matches so you get 169 (13 * 13).
It would probably help you visualize if you removed the aggregates. Still not quite sure what you are trying to do here but I can try to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/