Query Not Returning Correct Number of Records

Hi,
The following query that returns all distinct manager ids as well as the names of each manager from an employee table

SELECT DISTINCT M.CUID, M.FullName
FROM Employee AS M INNER JOIN Employee AS E ON M.CUID = E.MgrId
ORDER BY M.FullName;

Open in new window


It works fine.  I'm now attempting to a table called savings so that  that I return a list of distinct manager id in the savings table.  When I run the query only three manager id's are returned even though there are 4 distinct manager id in the savings table
Here is the query I've written.  
SELECT DISTINCT M.CUID, M.FullName
FROM (Employee AS M INNER JOIN Employee AS E ON M.CUID = E.MgrId) INNER JOIN Savings ON M.CUID = Savings.MgrID
ORDER BY M.FullName;

Open in new window


Thanks
chtullu135Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SimonConnect With a Mentor Commented:
I must admit I couldn't be sure of the schema details when I first posted - assumed it is something like the Northwind one with employee hierarchy structure indicated by MgrID.

I'd also try SELECT M.CUID, M.Fullname from Employee M where M.CUID in (SELECT MgrID from savings GROUP BY MgrID)

I'd also test that the 4 distinct MgrIDs in the savings table all exist in the employee table. Possible that records for ex-staff have been deleted?
0
 
SimonCommented:
I think line 2 of your second listing should have a different join condition:

FROM (Employee AS M INNER JOIN Employee AS E ON M.CUID = E.MgrId) INNER JOIN Savings ON E.MgrID = Savings.MgrID
0
 
chtullu135Author Commented:
Hello Simon,

I've tried it but it still only returns 3 records
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
HainKurtSr. System AnalystCommented:
if you select just from Employee , why you join all other tables?

i see you are joining with "inner join" so to get the same number you should join with "left join" but even if you join with "left join" i dont see why you use joins?

what do you get from here?

SELECT M.CUID, M.FullName FROM Employee

SELECT DISTINCT M.CUID, M.FullName FROM Employee

SELECT M.CUID, M.FullName
FROM Employee AS M INNER JOIN Employee AS E ON M.CUID = E.MgrId

SELECT M.CUID, M.FullName
FROM Employee AS M
INNER JOIN Employee AS E ON M.CUID = E.MgrId
INNER JOIN Savings AS S ON M.CUID = S.MgrID
0
 
chtullu135Author Commented:
I need to join on the savings tables in order to pull the unique managers from the savings table. This will be used to latter populate a cascading list box of unique managers. As an employee is selected, I want to see his or her manager in the manager list box.  If I select two employees, having the same manager, I only want to see that manager once in the manager list box
0
 
chtullu135Author Commented:
Hello Simon
"SELECT M.CUID, M.Fullname from Employee M where M.CUID in (SELECT MgrID from savings GROUP BY MgrID)" was the answer.  
This database does use employee hierarchy structure indicated by MgrID.  The employee originally did not use a
hierarchy structure.  When I started the contract I saw that people were entering the name of the manager into a manager name field.  In addition, multiple tables has  manager name, manager first name and last name fields.  I thought that was ridiculous so I created a manager Id field, and removed the manager name, manager first name and last name fields.  I could always get the manager name via a join so there was no sense in duplicating the same information in multiple tables
0
 
SimonCommented:
Thanks. Glad I could help.
0
 
HainKurtSr. System AnalystCommented:
Objection!

First of all this sql is meaningless

SELECT MgrID from savings GROUP BY MgrID

it should be just

select distinct MgrID from savings, even in the solution (?) above, no need by group by!

second, nobody should use "IN" instead of INNER JOIN. So, what you are looking for should be:

SELECT M.CUID, M.Fullname from Employee M INNER JOIN savings S ON M.CUID=S.MgrID
0
 
SimonCommented:
@ HainHurt - the SQL is not meaningless. Both "In()" and "inner join" work, and produce the same execution plan in most cases.  Likewise "Select Distinct" v "Group By" generally cost the same.
I think the OP may also have appreciated that I suggested another possible reason for the query not producing the expected number of results.
0
 
chtullu135Author Commented:
I appreciate everyone's input
0
All Courses

From novice to tech pro — start learning today.