求教一条涉及四个表的查询
四表如下：

server
+-----------+---------------+
|   GroupName   |   server_name       |  
+-----------+---------------+
|                 23   |   北南南北（东)   |

faction

Logtime         |   GroupName   |     id     |   faction_name               ||   masterid
-----------+-----------+------+--------------------++---------
2007-03-29   |                 11   |         1   |   这是测试问题名称       ||   11883888

user

|   Logtime         |   GroupName   |   masterid   |   usernamename|   id               |  
+------------+-----------+----------+-------------+------------+
|   2007-03-29   |                 11   |       1073       |   又一个名称     |     964               |


map

|   Logtime         |   GroupName   |   level   |   id         |
+------------+-----------+-------+-------+
|   2007-03-29   |                 11   |           1   |     5992   |

关系：四个表的Groupname都是相同的；faction.id和map.id是相同的；faction.masterid和user.masterid是相同的；faction.id和user.id和map.id是相同的


现在要得到这样的结果

server_name,faction_name,usernamename,level1数量，level2数量,level3数量

自己写了几个结果都不对，请大侠指教

------解决方案--------------------
select map.server_name,faction.faction_name,usernamename,
level1=(select count(1) from map where groupname=A.groupname and level=1)
level2=(select count(1) from map where groupname=A.groupname and level=2)
level3=(select count(1) from map where groupname=A.groupname and level=3)
from map A inner join server on A.groupname=server.groupname
inner join faction on A.groupname=faction.groupname
inner join user on A.groupname=user.groupname
------解决方案--------------------
SERVER表里的GroupName与其它表不符，改为11
faction.id和user.id和map.id是相同的（给出数据都不相同）都改为1另外，数据极像mysql的
CREATE TABLE server
(
GroupName INT,
server_name VARCHAR(50)
)
INSERT INTO SERVER
SELECT 11, '北南南北（东) '

CREATE TABLE faction
(
Logtime DATETIME,
GroupName INT,
ID INT,
faction_name VARCHAR(20),
masterid DECIMAL
)
INSERT INTO faction
SELECT '2007-03-29 ',11,1, '这是测试问题名称 ',11883888
CREATE TABLE [user]
(
Logtime DATETIME,
GroupName INT,
masterid INT,
usernamename VARCHAR(10),
ID INT
)
INSERT INTO [user]
SELECT '2007-03-29 ',11,1073, '又一个名称 ',1
CREATE TABLE map
(
Logtime DATETIME,
GroupName INT,
level INT,
ID INT
)
INSERT INTO map
SELECT '2007-03-29 ',11,1,1
GO


SELECT
server.server_name,
faction.faction_name,
[user].usernamename,
level1数量=(SELECT COUNT(1) FROM MAP WHERE LEVEL=1 AND ID=faction.id),
level2数量=(SELECT COUNT(1) FROM MAP WHERE LEVEL=2 AND ID=faction.id) ,
level3数量=(SELECT COUNT(1) FROM