Solved

# INNER JOIN between two tables

Posted on 2013-10-10
Medium Priority
566 Views
What is the difference between these two expressions?

select max(tblRegistration.ID)ID,max(Text4)Text4, max(total)total FROM tblRegistration INNER JOIN AttendanceGrid ON tblRegistration.[ID]=AttendanceGrid.[ID]
Where  Committee = 'lacausa' and tblRegistration.Fiscal = 2010 and month = 'June' group by tblRegistration.ID order by Text4

select distinct tblRegistration.ID, text4, total FROM tblRegistration INNER JOIN AttendanceGrid ON tblRegistration.[ID]=AttendanceGrid.[ID]
Where  Committee = 'lacausa' and tblRegistration.Fiscal = 2010 and month = 'June'  order by Text4

I would think they produce the same results when using the Distinct clause vs the group by clause?
0
Question by:al4629740
• 5
• 5

LVL 66

Expert Comment

ID: 39563017
Normally there wouldn't be any, but in this case in the first expression...

>select max(tblRegistration.ID)ID,max(Text4)Text4, max(total)total
>group by tblRegistration.ID

What's the purpose of the GROUP BY line if all columns in the SELECT clause are aggregate values, meaning no column is grouped?
0

LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 39563061
>select max(tblRegistration.ID)ID,max(Text4)Text4, max(total)total
Under normal circumsstances this would return a single row with the max of these three values.  Using GROUP BY would change that to however many tblRegistration.ID values there are as the number of rows, but without tblRegistration.ID in the SELECT I'm not sure how that would work.

>select distinct tblRegistration.ID, text4, total
This would return every unique combination of these three columns, with no Max-ing going on.
0

Author Comment

ID: 39563099
What does the Maxing do?
0

LVL 66

Expert Comment

ID: 39563120
Max(any column name) gives you the maximum value of 'any column name'.

btw I wrote an article that may be a good read:  SQL Server Group By solutions.  If it helps you please hit the 'Yes' button at the bottom.

0

Author Comment

ID: 39563169
Thank you
0

Author Comment

ID: 39563184
btw, does maxing take more processing power from the server to complete?
0

LVL 66

Expert Comment

ID: 39563189
Processing power, no, but it will take longer when you look at the query execution plan it involves a system aggregate.  What will come into play greater though will be the number of rows, whether they are character or numeric/date, and whether all columns involved are indexed.
0

Author Comment

ID: 39563556
Shouldn't it automatically index?
0

LVL 66

Expert Comment

ID: 39563599
Nope.   You have to explicitly state primary keys and indexes.
0

Author Comment

ID: 39563618
I opened a new question that I want your input on
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.