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

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 MAP WHERE LEVEL=3 AND ID=faction.id)

FROM server
INNER JOIN faction ON server.GroupName=faction.GroupName
INNER JOIN [user] ON faction.id=[user].id


drop TABLE server,faction,[user],map
--结果
server_name faction_name usernamename level1数量 level2数量 level3数量
------------ ---------------- ------------ ----------- --------- --------
北南南北（东) 这是测试问题名称 又一个名称 1 0 0

(1 行受影响)