SQL Statement Joints help.
Posted on 2004-09-03
I have the following query to display member's access rights. Some member they have 2 access staff as well as Mangement access. So i want to display only one name with the access management. But my query display two record for a staff member who has two access rights.
The Maccess table is like this. I am missing here to get only one record from this maccess table join with members table.
dbo.Members.MemberID, dbo.Members.LastName, dbo.Members.FirstName, dbo.Division.Division, dbo.Access.Obreviation, dbo.Access.Access
FROM dbo.Access INNER JOIN
dbo.MACCESS ON dbo.Access.AccessID = dbo.MACCESS.AccessID INNER JOIN
dbo.Members ON dbo.MACCESS.MemberID = dbo.Members.MemberID INNER JOIN
dbo.Division ON dbo.Members.Division = dbo.Division.DivisionID INNER JOIN
dbo.Status ON dbo.Members.Status = dbo.Status.StatusID INNER JOIN
dbo.DSIStatus ON dbo.Members.DSIStatus = dbo.DSIStatus.DSIStatusID
WHERE (dbo.SAStatus.Status = 'Staff') AND (dbo.Status.Status = 'A') ORDER BY dbo.access.access
Last name firstname memberid access
A B 1 MgtAccess
A B 1 Staff
But i want i.e. select only one record from the MAccess table join with the members table.
Lastname firstname memberid access
A B 1 mgtaccess