• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

SQL Statement Joints help.

Hi,

         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.

Memberid        accessID
1                        1
1                        2
2                        1
3                        1
4                        2

SELECT DISTINCT
                      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

It display

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

Thank you.
0
casstd
Asked:
casstd
  • 4
  • 3
  • 2
  • +3
1 Solution
 
ram2098Commented:
Add one more where clause to your query to get only Manager Access Records (Like..I assume ACCESSID in dbo.ACCESS = 1 for Manager Access ..then you can add it to the query as below...SEE THE LAST BUT ONE LINE)

SELECT DISTINCT
                      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')  AND (db.ACCESS.ACCESSID = 1)
 ORDER BY dbo.access.access
0
 
ram2098Commented:
I'm assuming that you need records only with Manager Access.
0
 
nexusSamCommented:
That won't work. He wants something that supresses "staff" access only when superceeded by "Manager" access

casstdl there are some inconsistencies with your column and table names so you need to check those (SAStatus is references in the WHERE at the end but is not JOINed earlier in the FROM - I assumes it was a typo for DSIStatus). Obviously without seeing all your table defs I can't judge

Try:
SELECT dbo.Members.MemberID, dbo.Members.LastName, dbo.Members.FirstName, dbo.Division.Division, dbo.Access.Obreviation, dbo.Access.Access
FROM         dbo.Access a, dbo.MACCESS m, dbo.Members mb, dbo.Division d, dbo.Status s, dbo.DSIStatus ds
WHERE a.AccessID = (SELECT MIN(AccessID) FROM dbo.MACCESS WHERE MemberID=mb.MemberID AND AccessID=a.AccessID)
AND mb.Division = d.DivisionID -- check the left side (is it DivisionID?)
AND mb.Status = s.StatusID
AND mb.DSIStatus = ds.DSIStatusID
AND  ds.Status = 'Staff' AND s.Status = 'A'
 ORDER BY a.access
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
ram2098Commented:
Thanks Nexussum for clarfication..

casstd, you can use the below query......(tring to minimise the changes from the original one..as you already tested this)

SELECT DISTINCT
                      dbo.Members.MemberID, dbo.Members.LastName, dbo.Members.FirstName, dbo.Division.Division, dbo.Access.Obreviation, dbo.Access.Access
FROM         dbo.Access
INNER JOIN (SELECT MIN(AccessID) ACCESSID, MEMBERID FROM dbo.MACCESS GROUP BY MEMBERID) A ON dbo.Access.AccessID = A.AccessID
INNER JOIN     dbo.Members ON A.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
0
 
Renante EnteraCommented:
Hello casstd!

You can have your query like this :

SELECT
  x.LastName, x.FirstName, x.MemberID, x.Access
FROM
(
SELECT
  DISTINCT 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
)
x
WHERE x.AccessID = 1

Try this one.  Hope it helps.


Goodluck!
eNTRANCE2002 :-)  
0
 
casstdAuthor Commented:
Hi,

        Sorry i explain clearly.

1. I want to display staff member list with their access. i.e all the active staff list (staff or mgt access or DM Access)

2. Some of the higher official staff have both staff and mgt access.

3. some of the managers will have staff and dm access.

So my Access table is like this

AcessID          Access
1                     Staff
2                     Mgt Access
3                     DM Access

My MAccess table is like this                                                          MY SIStatus table

MaccessID         Memberid          AccessID                                          SIStatusID           Status
1                            1                      1                                                     1                     Staff
2                            2                      1                                                     2                     Student
3                            1                      2
4                            3                      1
5                            2                      3
6                            4                      1

My member table is like this                                                                           Status table is like this

Memberid           lastname            firstname    SIStatus    status                      StatusID          Status
1                            Jhon                     A             1             1                             1                     Active
2                            Ram                     B             1             1                              2                      Left
3                            Shyam                 C              1             1
4                            Nathan                 D              1             1

My result should be like this (i.e i have to display one access of a staff)

Memberid          lastname          firstname           access
1                          John                    A                  Mgtaccess            
2                          Ram                    B                   DMAccess
3                          Shyam                C                    Staff
4                           Nathan               D                    Staff

SELECT DISTINCT
                      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.SIStatus = dbo.SIStatus.SIStatusID
WHERE     (dbo.SAStatus.Status = 'Staff') AND (dbo.Status.Status = 'A')   ORDER BY dbo.access.access


Hope i have clearly explain my question.

Many Thanks.

0
 
imrancsCommented:
SELECT DISTINCT
                      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 = (Select MAX(dbo.MACCESS.AccessID FROM  dbo.MACCESS WHERE MemberID = dbo.Members.MemberID ) 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.SIStatus = dbo.SIStatus.SIStatusID
WHERE     (dbo.SAStatus.Status = 'Staff') AND (dbo.Status.Status = 'A')   ORDER BY dbo.access.access
0
 
ram2098Commented:
Means..do you want to displat the Top most group he belongs to? (like if he has ..Staff,Manager,DMAcess..display as DM)

then you can use the following query....

SELECT DISTINCT
                      dbo.Members.MemberID, dbo.Members.LastName, dbo.Members.FirstName, dbo.Division.Division, dbo.Access.Obreviation, dbo.Access.Access
FROM         dbo.Access
INNER JOIN (SELECT MAX(AccessID) ACCESSID, MEMBERID FROM dbo.MACCESS GROUP BY MEMBERID) A ON dbo.Access.AccessID = A.AccessID
INNER JOIN     dbo.Members ON A.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
0
 
imrancsCommented:
Oops, right ')' missed of MAX function


SELECT DISTINCT
                      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 = (Select MAX(dbo.MACCESS.AccessID) FROM  dbo.MACCESS WHERE MemberID = dbo.Members.MemberID ) 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.SIStatus = dbo.SIStatus.SIStatusID
WHERE     (dbo.SAStatus.Status = 'Staff') AND (dbo.Status.Status = 'A')   ORDER BY dbo.access.access

0
 
AustinSevenCommented:
Imrancs... Unfortunately, I can't offer a solution as I don't seem to have the time to work it out but doesn't your last query produce this...

1      Jhon      A      DM Access
2      Ram      B      DM Access
3      Shyam      C      DM Access
4      Nathan      D      DM Access

??

AustinSeven
0
 
AustinSevenCommented:
Ok, here is what I think is required.  The 'distinct' isn't of any use so I dropped that.  The key thing is the subquery in th where clause.  Oh... if you copy the query to test it, change 'member' to 'members'.

select
     dbo.Member.MemberID, dbo.Member.LastName, dbo.Member.FirstName, dbo.Access.Access
from dbo.Access
        inner join dbo.MACCESS ON dbo.Access.AccessID = dbo.MACCESS.AccessID
        inner join dbo.Member ON dbo.MACCESS.MemberID = dbo.Member.MemberID
        inner join dbo.Status ON dbo.Member.Status = dbo.Status.StatusID
        inner join dbo.SIStatus ON dbo.Member.SIStatus = dbo.SIStatus.SIStatusID
where   (dbo.SIStatus.Status = 'staff') AND (dbo.Status.Status = 'Active') and
          dbo.MACCESS.AccessID = (select max( dbo.MACCESS.AccessID )
                                  from dbo.MACCESS
                                  where dbo.MACCESS.MemberID =  dbo.Member.MemberID)
group by dbo.Member.MemberID , dbo.Member.LastName, dbo.Member.FirstName, dbo.Access.Access

AustinSeven
0
 
casstdAuthor Commented:
Hi All,

              Thank you for your support and help.

0
 
AustinSevenCommented:
Did I  understand your question properly?  

I thought you wanted output as below?

1      Jhon      A      Mgt Access
2      Ram      B      DM Access
3      Shyam      C      staff
4      Nathan      D      staff

My query produces the above output (altghough I ignored the Division table as it wasn't central to the question).   Unless I'm missing something, the accepted answer does not produce any results.   I'm enquiring as much as anything else to learn something if I'm wrong.

AustinSeven

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now