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

SQL Join Query

SQL Join Query.

I am trying to query 3 tables in a database and I am not getting the results back that I am expecting.

Here is the query:

SELECT DISTINCT m.ADName, i.FirstName, i.LastName, r.Description
FROM         Membership AS m INNER JOIN
                         user_information AS i ON m.ADName = i.NT_Username INNER JOIN
                         RolesDescription AS r ON m.RoleID = r.RoleID
WHERE     (m.ApplicationID = 6)

I think its because a record does not exist in the "User_Information" (I would like the result to show a blank field if this is the case)

I am expecting 120 records but I am getting only 106.
0
SmashAndGrab
Asked:
SmashAndGrab
  • 7
  • 7
2 Solutions
 
ste5anSenior DeveloperCommented:
In this case use a LEFT JOIN:

SELECT m.ADName ,
       ISNULL(i.FirstName , '') AS FirstName,
       ISNULL(i.LastName , '') AS LastName,
       ISNULL(r.Description, '') AS Description
FROM   Membership m
       LEFT JOIN user_information i ON m.ADName = i.NT_Username
       LEFT JOIN RolesDescription r ON m.RoleID = r.RoleID
WHERE  m.ApplicationID = 6;

Open in new window

You're sure about the blanks?

Don't use DISTINCT without reason.
0
 
SmashAndGrabAuthor Commented:
Hi,

That result returns 569 records.
0
 
ste5anSenior DeveloperCommented:
Then you seem to have a reason for DISTINCT. But from the table names it seems to be the wrong one.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
SmashAndGrabAuthor Commented:
When I add the DISTINCT the query returns 119.
0
 
ste5anSenior DeveloperCommented:
In this case you should look at your data model.

Why are you one off?
0
 
SmashAndGrabAuthor Commented:
SELECT DISTINCT ADName AS MembershipID, RoleID, ApplicationID
FROM         Membership
WHERE     (ApplicationID = 6)

Returns: 119 records

SELECT   ADName AS MembershipID, RoleID, ApplicationID
FROM         Membership
WHERE     (ApplicationID = 6)

Returns: 120 records
0
 
SmashAndGrabAuthor Commented:
So this tells me that there must be 1 duplicate in the Membership table.

The Membership table is the driver.  I was expecting 120 records.
0
 
ste5anSenior DeveloperCommented:
This tells you, that (ADName, RoleID, ApplicationID) is not unique.

Should it be unique? Is it even a candidate key? If so, you should invest the time to fix this.
0
 
SmashAndGrabAuthor Commented:
Membership Table structure
0
 
SmashAndGrabAuthor Commented:
All Tables
Pseudo:

1. Bring back everything in the membership table.

2. For each record in the table:
               a: go and get the RoleID description from RolesDescription table
               b: Go and get the Users Firstname and lastname from the user_information table.


That's it.


A user can have multiple roles within an application (i.e. They can have read access, and also write access)
0
 
ste5anSenior DeveloperCommented:
If there is no further UNIQUE constraint or UNIQUE index on that table, I would guess it is a flaw, which should be corrected.
0
 
ste5anSenior DeveloperCommented:
From your relationship diagram: The combination (RoleID, ApplicationID, ADName) is a candidate key.
Further more, why isn't it the primary key?
Why is the column named ADName in the membership table and NT_Username in the user_information table.
Why the inconsistent naming? Why not UserInformation instead of user_information?
Why has RolesDescription no primary key? btw, shouldn't it be simply Roles?
0
 
SmashAndGrabAuthor Commented:
Hi,  Thanks for the comments.  

I am working on a legacy database.  If I were to be creating them now  - I would follow more consistent structures - as it stands - I have to work with what I have.
0
 
ste5anSenior DeveloperCommented:
I see. Brown field.

Well,I would at least considering to correct the duplicate issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now