Solved

SQL Statement Joints help.

Posted on 2004-09-03
13
504 Views
Last Modified: 2008-02-01
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
Comment
Question by:casstd
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 11

Expert Comment

by:ram2098
Comment Utility
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
 
LVL 11

Expert Comment

by:ram2098
Comment Utility
I'm assuming that you need records only with Manager Access.
0
 
LVL 2

Expert Comment

by:nexusSam
Comment Utility
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
 
LVL 11

Expert Comment

by:ram2098
Comment Utility
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
 
LVL 14

Expert Comment

by:Renante Entera
Comment Utility
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
 
LVL 6

Author Comment

by:casstd
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 10

Expert Comment

by:imrancs
Comment Utility
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
 
LVL 11

Accepted Solution

by:
ram2098 earned 125 total points
Comment Utility
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
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
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
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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
 
LVL 6

Author Comment

by:casstd
Comment Utility
Hi All,

              Thank you for your support and help.

0
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now