TBSupport
asked on
T-SQL: Subquery returned more than 1 value
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.DATEOFLAST REVIEW_I, ZACCU..RV010121.REVIEWSETU PNAME_I, ZACCU..RV010121.REVIEWEDBY _I, ZACCU..RV010121.APPROVEDBY _I, ZACCU..RV010121.DATEOFNEXT REVIEW_I,
ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME, ZACCU..UPR00100.DEPRTMNT, ZACCU..UPR00100.SUPERVISOR CODE_I,
ZACCU..UPR40300.DSCRIPTN, ZACCU..UPR41700.SUPERVISOR ,
(SELECT ZACCU..UPR00100.LASTNAME from ZACCU..UPR41700
INNER JOIN ZACCU..UPR00100
on
ZACCU..UPR41700.SUPERVISOR CODE_I = ZACCU..UPR00100.SUPERVISOR CODE_I
WHERE ZACCU..UPR41700.SUPERVISOR CODE_I = ZACCU..UPR00100.SUPERVISOR CODE_I)
AS [SUPERLAST],
(SELECT ZACCU..UPR00100.FRSTNAME from ZACCU..UPR41700
INNER JOIN ZACCU..UPR00100
on
ZACCU..UPR41700.SUPERVISOR CODE_I = ZACCU..UPR00100.SUPERVISOR CODE_I
WHERE ZACCU..UPR41700.SUPERVISOR CODE_I = ZACCU..UPR00100.SUPERVISOR CODE_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.SUPERVISOR CODE_I = ZACCU..UPR00100.SUPERVISOR CODE_I
SELECT ZACCU..UPR41700.SUPERVISOR CODE_I,
ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME from ZACCU..UPR41700
INNER JOIN ZACCU..UPR00100
on
ZACCU..UPR41700.SUPERVISOR CODE_I = ZACCU..UPR00100.SUPERVISOR CODE_I
WHERE ZACCU..UPR41700.SUPERVISOR CODE_I = ZACCU..UPR00100.SUPERVISOR CODE_I
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.DATEOFLAST
ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME, ZACCU..UPR00100.DEPRTMNT, ZACCU..UPR00100.SUPERVISOR
ZACCU..UPR40300.DSCRIPTN, ZACCU..UPR41700.SUPERVISOR
(SELECT ZACCU..UPR00100.LASTNAME from ZACCU..UPR41700
INNER JOIN ZACCU..UPR00100
on
ZACCU..UPR41700.SUPERVISOR
WHERE ZACCU..UPR41700.SUPERVISOR
AS [SUPERLAST],
(SELECT ZACCU..UPR00100.FRSTNAME from ZACCU..UPR41700
INNER JOIN ZACCU..UPR00100
on
ZACCU..UPR41700.SUPERVISOR
WHERE ZACCU..UPR41700.SUPERVISOR
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.SUPERVISOR
SELECT ZACCU..UPR41700.SUPERVISOR
ZACCU..UPR00100.LASTNAME, ZACCU..UPR00100.FRSTNAME from ZACCU..UPR41700
INNER JOIN ZACCU..UPR00100
on
ZACCU..UPR41700.SUPERVISOR
WHERE ZACCU..UPR41700.SUPERVISOR
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
ORSELECT 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
ASKER
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
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
If you look at your subquery:
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 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],
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],
ASKER
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
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
>> 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.
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.
ASKER
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.
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.
Here is my test bed with assumed table structures and fake data:
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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.
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.
ASKER
The solutions that I received from the others were very much appreciated but did not derive the desired values.
for. e.g.
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: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.