不用not in和not exist能实现这样的sql吗？
有这样几个表：
角色表：Role；字段有：ID,Name
权限表：Permission；字段有：ID,Name，PermissionTypeID
权限类别表：PermissionType；字段有：ID，TypeName
角色权限表：RolePermission；字段有：ID,RoleID,PermissionID

我想查询这样的权限，某个角色在某个权限类别中   拥有的权限   和   还没有拥有的权限，输入的比较参数就是Role的ID和PermissionType的ID，拥有的权限好查些，但没拥有的权限不怎么好查，，我用not   in实现了，   不过sql代码好长，不知道你们你怎么解决的

------解决方案--------------------

角色权限表：RolePermission；字段有：ID,RoleID,PermissionID
最好还有个权限表。要是没有独立的权限表也可以这样查寻一个角色没有的权限：
SELECT DISTINCT PermissionID
FROM RolePermission AS A
WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS B WHERE B.RoleID=@RoleID AND B.PermissionID=A.PermissionID)
--@RoleID是要找的角色

------解决方案--------------------
修改一下我原来的语句就可以，
try:

SELECT DISTINCT PermissionID
FROM RolePermission AS A
INNER JOIN Role AS B ON B.[id]=A.RoleID AND PermissionTypeID=@PermissionTypeID
WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS B WHERE B.RoleID=@RoleID AND B.PermissionID=A.PermissionID)

--@RoleID是要找的角色
--@PermissionTypeID 是要类型ID

------解决方案--------------------
--查已有权限
select t1.*
from (
select r.id as RoleID,t.id as PermissionTypeID
from Role r
cross join PermissionType t
) as t1
left join (
select r.id as RoleID,t.id as PermissionTypeID
from Role r
inner join RolePermission rp
on r.id=rp.RoleID
inner join Permission p
on rp.PermissionID=p.ID
inner join PermissionType t
on p.PermissionTypeID=t.ID
) as t2
on t1.RoleID=t2.RoleID and t1.PermissionTypeID=t2.PermissionTypeID
where t2.RoleID is not null

--查没有的权限
select t1.*
from (
select r.id as RoleID,t.id as PermissionTypeID
from Role r
cross join PermissionType t
) as t1
left join (
select r.id as RoleID,t.id as PermissionTypeID
from Role r
inner join RolePermission rp
on r.id=rp.RoleID
inner join Permission p
on rp.PermissionID=p.ID
inner join PermissionType t
on p.PermissionTypeID=t.ID
) as t2
on t1.RoleID=t2.RoleID and t1.PermissionTypeID=t2.PermissionTypeID
where t2.RoleID is null