All,
I have a perplexing problem that I hope someone can help me with.
I have the following table struct:
Permission
-----------------
PermissionId
Permission
Description
UserPermission
-----------------
PermissionId
UserId
Active
I am attempting to retrieve all records from the permission table
whether there is a match on UserPermission.PermissionId or not.
Therefore I implemented this query, which does not produce the results
that I expect:
SELECT p.Permission,
up.Active
FROM Permission p
LEFT OUTER JOIN UserPermission up
ON p.[Id] = up.PermissionId
WHERE up.UserId = 3
However, if I exec this query, it works as it is supposed to:
SELECT p.Permission,
up.Active
FROM Permission p, UserPermission up
WHERE p.[Id] *= up.PermissionId AND up.UserId = 3
In the first query, only the records that match on "permissionId" are
returned, in the second all records are returned from the Left table
and those records that do not have matching columns are set to null, as
it should be. My question is, what have I done wrong here?
I am running MS-SQLServer 2000