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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
In this case use a LEFT JOIN:

       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.
SmashAndGrabAuthor Commented:

That result returns 569 records.
ste5anSenior DeveloperCommented:
Then you seem to have a reason for DISTINCT. But from the table names it seems to be the wrong one.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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

Why are you one off?
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
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SmashAndGrabAuthor Commented:
Membership Table structure
SmashAndGrabAuthor Commented:
All Tables

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)
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.
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?
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.
ste5anSenior DeveloperCommented:
I see. Brown field.

Well,I would at least considering to correct the duplicate issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.