Link to home
Start Free TrialLog in
Avatar of SmashAndGrab
SmashAndGrab

asked on

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.
Avatar of ste5an
ste5an
Flag of Germany image

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.
Avatar of SmashAndGrab
SmashAndGrab

ASKER

Hi,

That result returns 569 records.
Then you seem to have a reason for DISTINCT. But from the table names it seems to be the wrong one.
When I add the DISTINCT the query returns 119.
In this case you should look at your data model.

Why are you one off?
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
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.
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
User generated image
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)
If there is no further UNIQUE constraint or UNIQUE index on that table, I would guess it is a flaw, which should be corrected.
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?
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial