Solved

Query Not Returning Correct Number of Records

Posted on 2014-11-22
10
119 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:SimonAdept
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:
SimonAdept 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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:SimonAdept
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:SimonAdept
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 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

20 Experts available now in Live!

Get 1:1 Help Now