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.
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.
DatabasesStatistical PackagesRSQL
Last Comment
ste5an
ASKER
Hi,
That result returns 569 records.
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.
ASKER
When I add the DISTINCT the query returns 119.
In this case you should look at your data model.
Why are you one off?
Why are you one off?
ASKER
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
FROM Membership
WHERE (ApplicationID = 6)
Returns: 119 records
SELECT ADName AS MembershipID, RoleID, ApplicationID
FROM Membership
WHERE (ApplicationID = 6)
Returns: 120 records
ASKER
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.
The Membership table is the driver. I was expecting 120 records.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
ASKER
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?
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?
ASKER
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Open in new window
You're sure about the blanks?Don't use DISTINCT without reason.