al4629740
asked on
INNER JOIN between two tables
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]=Atten danceGrid. [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]=Atten danceGrid. [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?
select max(tblRegistration.ID)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]=Atten
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What does the Maxing do?
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.
Thanks for the grade. Good luck with your project. -Jim
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.
Thanks for the grade. Good luck with your project. -Jim
ASKER
Thank you
ASKER
btw, does maxing take more processing power from the server to complete?
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.
ASKER
Shouldn't it automatically index?
Nope. You have to explicitly state primary keys and indexes.
ASKER
I opened a new question that I want your input on
>select max(tblRegistration.ID)ID,
>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?