Solved

SQL Statement Joints help.

Posted on 2004-09-03
13
516 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 11

Expert Comment

by:ram2098
ID: 11970936
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
ID: 11970939
I'm assuming that you need records only with Manager Access.
0
 
LVL 2

Expert Comment

by:nexusSam
ID: 11970992
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 11

Expert Comment

by:ram2098
ID: 11971037
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
ID: 11971043
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
ID: 11971068
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
 
LVL 10

Expert Comment

by:imrancs
ID: 11971195
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
ID: 11971202
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
ID: 11971203
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
ID: 11972936
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
ID: 11973416
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
ID: 11986773
Hi All,

              Thank you for your support and help.

0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 11989129
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

732 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