?
Solved

T-SQL:  Subquery returned more than 1 value

Posted on 2013-10-30
12
Medium Priority
?
873 Views
Last Modified: 2013-11-04
Hello:

Upon running a T-SQL select statement that I just now composed, I'm getting the following error:  "Subquery returned more than 1 value.  This is not permitted...".

I know that it has to do with the fact that I'm using a subquery to get the last name and first name of the supervisor, and I know that it has to do with the fact that there is more than one value in the subquery.  But, I don't know how to get rid of this error.  

I have called the two fields "SUPERLAST" and "SUPERFIRST".

The following is the select statement that I need to fix and, afterward, I have another select statement showing the query that the two subqueries are based on.  

Please help.  Thanks!

TBSupport

select ZACCU..RV010121.EMPID_I, ZACCU..RV010121.DATEOFLASTREVIEW_I, ZACCU..RV010121.REVIEWSETUPNAME_I, ZACCU..RV010121.REVIEWEDBY_I, ZACCU..RV010121.APPROVEDBY_I, ZACCU..RV010121.DATEOFNEXTREVIEW_I,
ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME, ZACCU..UPR00100.DEPRTMNT, ZACCU..UPR00100.SUPERVISORCODE_I,
ZACCU..UPR40300.DSCRIPTN, ZACCU..UPR41700.SUPERVISOR,
(SELECT ZACCU..UPR00100.LASTNAME from ZACCU..UPR41700
 INNER JOIN ZACCU..UPR00100
 on
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)
 AS [SUPERLAST],  
 (SELECT ZACCU..UPR00100.FRSTNAME from ZACCU..UPR41700
 INNER JOIN ZACCU..UPR00100
 on
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)
 AS [SUPERFIRST]
from ZACCU..RV010121  
 INNER JOIN ZACCU..UPR00100
 on
ZACCU..UPR00100.EMPLOYID = ZACCU..RV010121.EMPID_I
 INNER JOIN ZACCU..UPR40300
 on
 ZACCU..UPR40300.DEPRTMNT = ZACCU..UPR00100.DEPRTMNT
 INNER JOIN ZACCU..UPR41700
 on
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I


 SELECT ZACCU..UPR41700.SUPERVISORCODE_I,
 ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME from ZACCU..UPR41700
 INNER JOIN ZACCU..UPR00100
 on
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
0
Comment
Question by:TBSupport
[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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 39612081
As you mentioned already, try running the subqueries for SUPERLAST and SUPERFIRST,

for. e.g.

(SELECT ZACCU..UPR00100.LASTNAME from ZACCU..UPR41700 
 INNER JOIN ZACCU..UPR00100
 on 
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)

Open in new window

individually, or an alternative is to try this, if its ok for you (top 1), if the query criteria and joins are perfect, and still there is chance that sub query returns multiple rows, then try TOP 1:
(SELECT TOP 1 ZACCU..UPR00100.LASTNAME from ZACCU..UPR41700 
 INNER JOIN ZACCU..UPR00100
 on 
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I).

Open in new window

As i could not understand your full query, could not suggest on any alternate ways to get your data.

Hope that helps.
0
 
LVL 18

Expert Comment

by:lludden
ID: 39612125
You need to refer to the table in the outer query in the where clause of the inner query.

SELECT Z1.EMPID_I,
	   Z1.DATEOFLASTREVIEW_I,
	   Z1.REVIEWSETUPNAME_I,
	   Z1.REVIEWEDBY_I,
	   Z1.APPROVEDBY_I,
	   Z1.DATEOFNEXTREVIEW_I,
	   Z2.LASTNAME,
	   Z2.FRSTNAME,
	   Z2.DEPRTMNT,
	   Z2.SUPERVISORCODE_I,
	   Z3.DSCRIPTN,
	   Z4.SUPERVISOR,
	   (SELECT Z02.LASTNAME 
		FROM ZACCU..UPR41700 Z04
			INNER JOIN ZACCU..UPR00100 Z02 ON Z04.SUPERVISORCODE_I = Z02.SUPERVISORCODE_I
		WHERE Z04.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I) AS [SUPERLAST],
	   (SELECT Z002.FRSTNAME
		FROM ZACCU..UPR41700 Z004
			INNER JOIN ZACCU..UPR00100 Z002	ON Z004.SUPERVISORCODE_I = Z002.SUPERVISORCODE_I
		WHERE
			Z004.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I) AS [SUPERFIRST]
FROM
	ZACCU..RV010121 Z1
	INNER JOIN ZACCU..UPR00100 Z2 ON Z2.EMPLOYID = Z1.EMPID_I
	INNER JOIN ZACCU..UPR40300 Z3 ON Z3.DEPRTMNT = Z2.DEPRTMNT
	INNER JOIN ZACCU..UPR41700 Z4 ON Z4.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I

Open in new window

OR
SELECT Z1.EMPID_I,
	   Z1.DATEOFLASTREVIEW_I,
	   Z1.REVIEWSETUPNAME_I,
	   Z1.REVIEWEDBY_I,
	   Z1.APPROVEDBY_I,
	   Z1.DATEOFNEXTREVIEW_I,
	   Z2.LASTNAME,
	   Z2.FRSTNAME,
	   Z2.DEPRTMNT,
	   Z2.SUPERVISORCODE_I,
	   Z3.DSCRIPTN,
	   Z4.SUPERVISOR,
	   SUPNAME.LASTNAME AS [SUPERLAST],
	   SUPNAME.FIRSTNAME AS [SUPERFIRST]
FROM
	ZACCU..RV010121 Z1
	INNER JOIN ZACCU..UPR00100 Z2 ON Z2.EMPLOYID = Z1.EMPID_I
	INNER JOIN ZACCU..UPR40300 Z3 ON Z3.DEPRTMNT = Z2.DEPRTMNT
	INNER JOIN ZACCU..UPR41700 Z4 ON Z4.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I
	CROSS APPLY (SELECT Z02.LASTNAME, Z02.FIRSTNAME
		FROM ZACCU..UPR41700 Z04
			INNER JOIN ZACCU..UPR00100 Z02 ON Z04.SUPERVISORCODE_I = Z02.SUPERVISORCODE_I
		WHERE Z04.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I) AS SupName

Open in new window

0
 
LVL 1

Author Comment

by:TBSupport
ID: 39612137
I had already tried TOP 1.  That only pulled in one supervisor.

I know what the subqueries do.  So, running them individually does not help.  

I need to know how to put these subqueries into my main query to get the first name and last name of the supervisor in the UPR41700 table, without getting this error.  

The supervisor code ID in that table matches that of the UPR00100 table.

TBSupport
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 18

Expert Comment

by:lludden
ID: 39612183
If you look at your subquery:
(SELECT ZACCU..UPR00100.LASTNAME from ZACCU..UPR41700 
 INNER JOIN ZACCU..UPR00100
 on 
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)
 AS [SUPERLAST],  

Open in new window


The WHERE clause is not referring back to the parent query.  You need to at least use an alias in this query so the query parser knows you want the SupervisorCode from the parent query, rather than the one from this query.
(SELECT Z01.LASTNAME from ZACCU..UPR41700 Z02
 INNER JOIN ZACCU..UPR00100 Z01
 on 
 Z02.SUPERVISORCODE_I = Z01.SUPERVISORCODE_I
WHERE Z02.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)
 AS [SUPERLAST],  

Open in new window

0
 
LVL 1

Author Comment

by:TBSupport
ID: 39612356
Hi lludden:

The first of the two queries that you suggested to try gave me the same error., while the second query gave me too many records--thousands instead of 298.

Any ideas?

TBSupport
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39612764
>> I had already tried TOP 1.  That only pulled in one supervisor.
What are you expecting in case of multiple values? Are those multiple LASTNAME/FRSTNAME returned from sub-query contain duplicates? or different values.
You can avoid all additional joins/sub-queries. Provide some sample data from all your tables and expected result.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39612793
There are different supervisors for the different employees.  Supervisors are in the UPR41700 table but there names are not given.  All employees, including supervisors, are in the UPR00100 table and the last and first names of all employees are only in UPR00100.
0
 
LVL 18

Expert Comment

by:lludden
ID: 39612808
It is much easier to test with some sample data, but this should work. Based on what I could reverse engineer from the context of the data, here is what probably will work.

SELECT Z1.EMPID_I,
	   Z1.DATEOFLASTREVIEW_I,
	   Z1.REVIEWSETUPNAME_I,
	   Z1.REVIEWEDBY_I,
	   Z1.APPROVEDBY_I,
	   Z1.DATEOFNEXTREVIEW_I,
	   Z2.LASTNAME,
	   Z2.FRSTNAME,
	   Z2.DEPRTMNT,
	   Z2.SUPERVISORCODE_I,
	   Z3.DSCRIPTN,
	   Z4.SUPERVISOR,
	   SUPNAME.LASTNAME AS [SUPERLAST],
	   SUPNAME.FIRSTNAME AS [SUPERFIRST]
FROM
	ZACCU..RV010121 Z1
	INNER JOIN ZACCU..UPR00100 Z2 ON Z2.EMPLOYID = Z1.EMPID_I
	INNER JOIN ZACCU..UPR40300 Z3 ON Z3.DEPRTMNT = Z2.DEPRTMNT
	INNER JOIN ZACCU..UPR41700 Z4 ON Z4.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I
	CROSS APPLY (SELECT Z02.LASTNAME, Z02.FIRSTNAME
		FROM ZACCU..UPR41700 Z04
			INNER JOIN ZACCU..UPR00100 Z02 ON Z04.SUPERVISORCODE_I = Z02.EMPLOYID
		WHERE Z04.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I) AS SupName

Open in new window


Here is my test bed with assumed table structures and fake data:
DECLARE @T1 TABLE (EMPID_I INTEGER, DATEOFLASTREVIEW_I DATE, REVIEWSETUPNAME_I varchar(20), REVIEWEDBY_I VARCHAR(20), APPROVEDBY_I VARCHAR(20), DATEOFNEXTREVIEW_I DATE)
INSERT INTO @T1 (EMPID_I,DATEOFLASTREVIEW_I,REVIEWSETUPNAME_I,REVIEWEDBY_I,APPROVEDBY_I,DATEOFNEXTREVIEW_I) VALUES (1, '20130101','Review1','Reviewer1','Approved1','20130601')
INSERT INTO @T1 (EMPID_I,DATEOFLASTREVIEW_I,REVIEWSETUPNAME_I,REVIEWEDBY_I,APPROVEDBY_I,DATEOFNEXTREVIEW_I) VALUES (2, '20130201','Review2','Reviewer2','Approved1','20130701')
INSERT INTO @T1 (EMPID_I,DATEOFLASTREVIEW_I,REVIEWSETUPNAME_I,REVIEWEDBY_I,APPROVEDBY_I,DATEOFNEXTREVIEW_I) VALUES (3, '20130301','Review3','Reviewer1','Approved2','20130801')
INSERT INTO @T1 (EMPID_I,DATEOFLASTREVIEW_I,REVIEWSETUPNAME_I,REVIEWEDBY_I,APPROVEDBY_I,DATEOFNEXTREVIEW_I) VALUES (4, '20130401','Review4','Reviewer2','Approved2','20130901')

DECLARE @T2 TABLE (EMPLOYID INT, LASTNAME VARCHAR(20), FIRSTNAME VARCHAR(20), DEPRTMNT INT, SUPERVISORCODE_I INT)
INSERT INTO @T2 VALUES (1,'Smith','Tom',1,20)
INSERT INTO @T2 VALUES (2,'Jones','Mike',2,21)
INSERT INTO @T2 VALUES (3,'Wilson','Jo',2,20)
INSERT INTO @T2 VALUES (4,'Jennings','Marge',1,21)
INSERT INTO @T2 VALUES (20,'Sherry','Brown',1,0)
INSERT INTO @T2 VALUES (21,'Mike','Ford',1,0)


DECLARE @T3 TABLE (DEPRTMNT INT, DSCRIPTN VARCHAR(20))
INSERT INTO @T3 VALUES (1,'Sales')
INSERT INTO @T3 VALUES (2,'Marketing')

DECLARE @T4 TABLE (SUPERVISORCODE_I INT, Supervisor VARCHAR(20) )
INSERT INTO @T4 VALUES (20,'SherryBrown')
INSERT INTO @T4 VALUES (21,'MikeFord')

SELECT Z1.EMPID_I,
	   Z1.DATEOFLASTREVIEW_I,
	   Z1.REVIEWSETUPNAME_I,
	   Z1.REVIEWEDBY_I,
	   Z1.APPROVEDBY_I,
	   Z1.DATEOFNEXTREVIEW_I,
	   Z2.LASTNAME,
	   Z2.FIRSTNAME,
	   Z2.DEPRTMNT,
	   Z2.SUPERVISORCODE_I,
	   Z3.DSCRIPTN,
	   Z4.SUPERVISOR,
	   SUPNAME.LASTNAME AS [SUPERLAST],
	   SUPNAME.FIRSTNAME AS [SUPERFIRST]
FROM
	@T1 Z1
	INNER JOIN @T2 Z2 ON Z2.EMPLOYID = Z1.EMPID_I
	INNER JOIN @T3 Z3 ON Z3.DEPRTMNT = Z2.DEPRTMNT
	INNER JOIN @T4 Z4 ON Z4.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I
	CROSS APPLY (SELECT Z02.LASTNAME, Z02.FIRSTNAME
		FROM @T4 Z04
			INNER JOIN @T2 Z02 ON Z04.SUPERVISORCODE_I = Z02.EMPLOYID
		WHERE Z04.SUPERVISORCODE_I = Z2.SUPERVISORCODE_I) AS SupName

Open in new window

0
 
LVL 1

Author Comment

by:TBSupport
ID: 39612836
Hi lludden:

That returns no data.

I think that if I can somehow add the UPR00100 table "twice" inside the master query, that will derive what I'm looking for.  I'm just not adept enough at T-SQL to know how to do so.

TBSupport
0
 
LVL 1

Accepted Solution

by:
TBSupport earned 0 total points
ID: 39612852
Here is the answer!  I got it, per my most recent comment:

select ZACCU..RV010121.EMPID_I, ZACCU..RV010121.DATEOFLASTREVIEW_I, ZACCU..RV010121.REVIEWSETUPNAME_I, ZACCU..RV010121.REVIEWEDBY_I, ZACCU..RV010121.APPROVEDBY_I, ZACCU..RV010121.DATEOFNEXTREVIEW_I,
ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME, ZACCU..UPR00100.DEPRTMNT, ZACCU..UPR00100.SUPERVISORCODE_I,
ZACCU..UPR40300.DSCRIPTN, ZACCU..UPR41700.SUPERVISOR,
--(SELECT ZACCU..UPR00100.LASTNAME from ZACCU..UPR41700
-- INNER JOIN ZACCU..UPR00100
-- on
-- ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
--WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)
b.LASTNAME AS [SUPERLAST],  
-- (SELECT ZACCU..UPR00100.FRSTNAME from ZACCU..UPR41700
-- INNER JOIN ZACCU..UPR00100
-- on
-- ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
--WHERE ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I)
b.FRSTNAME AS [SUPERFIRST]
from ZACCU..RV010121  
 INNER JOIN ZACCU..UPR00100
 on
ZACCU..UPR00100.EMPLOYID = ZACCU..RV010121.EMPID_I
 INNER JOIN ZACCU..UPR40300
 on
 ZACCU..UPR40300.DEPRTMNT = ZACCU..UPR00100.DEPRTMNT
 INNER JOIN ZACCU..UPR41700
 on
 ZACCU..UPR41700.SUPERVISORCODE_I = ZACCU..UPR00100.SUPERVISORCODE_I
 INNER JOIN ZACCU..UPR00100 b
 on ZACCU..UPR41700.EMPLOYID = b.EMPLOYID
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39612861
>> The solutions that I received from the others were very much appreciated but did not derive the desired values.
This is because you did not provide more information. If you provide some sample data, it would be easy. You know very much about your data than anyone else.
0
 
LVL 1

Author Closing Comment

by:TBSupport
ID: 39621094
The solutions that I received from the others were very much appreciated but did not derive the desired values.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 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