Solved

Query Not Returning Correct Number of Records

Posted on 2014-11-22
10
121 Views
Last Modified: 2014-11-23
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
0
Comment
Question by:chtullu135
  • 4
  • 4
  • 2
10 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40459948
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
 

Author Comment

by:chtullu135
ID: 40459967
Hello Simon,

I've tried it but it still only returns 3 records
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40459972
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
 

Author Comment

by:chtullu135
ID: 40459977
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
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40459994
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:chtullu135
ID: 40460002
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
 
LVL 18

Expert Comment

by:Simon
ID: 40460017
Thanks. Glad I could help.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40460281
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
 
LVL 18

Expert Comment

by:Simon
ID: 40460517
@ 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
 

Author Comment

by:chtullu135
ID: 40460642
I appreciate everyone's input
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now