Group by on a select statement, SQL 2005

I have a query that is acting really funny. I am joining two tables.  When I use a where clause everything comes back fine.  When I add a group by clause I get no results.  Is there a setting that I am missing?   Also, If i run the statement with set ansi_nulls off it works.  I am not using any isnull or comparing any nulls.  I am basically selecting columns.  If I remove the group by it works.

select table1.column1
from table1
inner join table2
  on table1.column1 = table2 = column2
group by .....
yanci1179Asked:
Who is Participating?
 
vipin_nagarroConnect With a Mentor Commented:
Hi
May be one of the column in a table u are using for join have no values, which will return nothing when ANSI_NULL is ON and will return it if u set it off.
This is a data problem, check the data in all the columns u r using of all the tables in query.
The Story here is :
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column.
Refer: http://doc.ddart.net/mssql/sql70/set-set_5.htm
Thanks and Regards
Vipin Goel
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
not sure what exactly your issue is unless you privide us the  exact queries and sample results

select table1.column1
from table1
inner join table2
  on table1.column1 = table2.column2
group by  table1.column1
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
where and Group by, both are different in functionality, one for filtering and the other for Grouping ,
0
 
yanci1179Author Commented:
Please see the attached queries.  I have attached the ones that work and those that do not work
weirdQueries.sql
0
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.

All Courses

From novice to tech pro — start learning today.