Solved

Query Not Returning Correct Number of Records

Posted on 2014-11-22
10
130 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 55

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 55

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

707 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