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
Solved

SQL Statement Joints help.

Posted on 2004-09-03
13
512 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
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

860 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