GROUP BY in SQL Server across three tables

Question

I have found that I don't know how to group results of query in SQL Server. I have SQL Server 2005 with three tables:

domains:

- id
- name

entity:

- domain_id
- user_id

users:

- id
- login

So, it's common relationship between two tables across third table, where several domains may relate to one user.

T-SQL query:

SELECT domain.name, users.login 
FROM domain, entity, users 
WHERE domain.id = entity.domain_id AND entity.user_id = users.id 
GROUP BY users.login, domain.name

returns:

+------+-------+
| name | login |
+------+-------+
| dom1 | user1 |
| dom4 | user1 |
| dom5 | user1 |
| dom2 | user2 |
| dom3 | user3 |
+------+-------+

My question is how to create T-SQL query which returns same as MySQL:

SELECT domain.name, user.login 
FROM domain, entity, user 
WHERE domain.id = entity.domain_id AND entity.user_id = user.id 
GROUP BY user.login

+------+-------+
| name | login |
+------+-------+
| dom5 | user1 |
| dom2 | user2 |
| dom3 | user3 |
+------+-------+

Your query should work in both MySQL and SQL Server. Is it possible that the content of your tables is different? — Andomar, Aug 11 '12 at 11:13
@Andomar: The 2nd query has the `domain.name` missing from the `GROUP BY`. — ypercubeᵀᴹ, Aug 11 '12 at 11:19
@ypercube: Right, surprising tho, since I'd expect SQL Server to return an error for the missing `group by` column — Andomar, Aug 11 '12 at 11:26
I don't understand what you are grouping for. What information are you trying to get from this data? — Jim, Aug 11 '12 at 15:08
Jim, I need a list of domains grouped by users. I don't know how to explain more clear. It's ok if not all domains will be selected, it's necessary for me because in my code I have to access to each user, but i can do this only via domain, and it doesn't matter which one. — Oleg Neumyvakin, Aug 11 '12 at 15:53

podiluska · Accepted Answer · 2012-08-11T11:57:41.957

To create a MS SQL (and most standard implementations) that returns the same as MySQL

SELECT MAX(domain.name), user.login  
FROM domain, entity, user  
WHERE domain.id = entity.domain_id AND entity.user_id = user.id  
GROUP BY user.login

In both environments, the FROM / WHERE join would be better expressed as

FROM domain
    INNER JOIN entity
         ON domain.id = entity.domain_id 
    INNER JOIN user  
         ON entity.user_id = user.id

GROUP BY in SQL Server across three tables

1 Answers1