Solved

Query Not Returning Correct Number of Records

Posted on 2014-11-22
10
129 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 53

Expert Comment

by:Huseyin KAHRAMAN
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
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.

 

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
 

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 53

Expert Comment

by:Huseyin KAHRAMAN
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

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

732 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