Solved

SQL Statement Joints help.

Posted on 2004-09-03
13
515 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrade SQL Server 2014 Standard Edition from SQL Server 2012 Standard Edition 14 37
Star schema daily updates 2 35
SQL Server Resume 5 45
Convert VBA UDF to SQl SERVER UDF 4 49
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 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